Oracle 新手错误：ORA-00904 使用"大小写"时标识符无效

Question

我在查询中得到错误。这个查询可以，并且返回数据(select和rownums用于分页):

select *
from (select a.*, rownum rnum
from (select id_edition, id_document, name, extension, creation_date, url, 
(select inscription_date from edition_student_d0 where id_edition = 12345 and id_third =     12345) inscription_date
from upd_edition_doc_d0
where id_edition = 1071591
order by creation_date desc) a
where rownum <= 10 )
where rnum >= 1

现在我试图包括一个"case when"，并在某些情况下获取url，所以我做了这些修改，包括一个case块:

select *
from (select a.*, rownum rnum
from (select id_edition, id_document, name, extension, creation_date, 
(select inscription_date from edition_student_d0 where id_edition = 12345 and id_third = 12345) inscription_date,
case
when trunc(inscription_date + 90) <= trunc(sysdate) then null
else url
end url
from upd_edition_doc_d0
where id_edition = 1071591
order by creation_date desc) a
where rownum <= 10 )
where rnum >= 1

Oracle启动此错误:

ORA-00904: "INSCRIPTION_DATE": invalid identifier

我想这是因为我要求inscription_date并在相同的查询级别使用它，但我不知道如何处理这个

还有，我怎么做我想做的?我的意思是，只有在特定条件下才能获得url。

有人能帮忙吗?

提前感谢。

Answer 1

不能在同一查询级别引用别名。

您可以替换子查询…

select *
from (select a.*, rownum rnum
  from (select id_edition, id_document, name, extension, creation_date, 
           (select inscription_date from edition_student_d0 where id_edition = 12345 and  id_third = 12345) inscription_date,
           case when trunc((select inscription_date from edition_student_d0 where id_edition = 12345 and  id_third = 12345) + 90) <= trunc(sysdate) then null
             else url
           end as url
           from upd_edition_doc_d0
           where id_edition = 1071591
           order by creation_date desc) a
   where rownum <= 10 )
where rnum >= 1

或将大小写向上移动一级。

select *
from (select a.*, 
      case when trunc(inscription_date + 90) <= trunc(sysdate) then null
       else url
      end as url,
      rownum rnum
  from (select id_edition, id_document, name, extension, creation_date, 
           (select inscription_date from edition_student_d0 where id_edition = 12345 and  id_third = 12345) inscription_date
           from upd_edition_doc_d0
           where id_edition = 1071591
           order by creation_date desc) a
   where rownum <= 10 )
where rnum >= 1