Oracle中WITH子句定义日期变量报错，数值变量正常的原因咨询

首先咱们拆解下你遇到的两个情况的核心差异，以及报错的根本原因：

1. 第一个SQL“看似正常”的真相 #

你给出的第一个SQL：

with a as( select 100 as query_rows from dual ) ,b as ( select * from table1 where rownum=query_rows ) select * from b

严格来说这个写法其实不应该正常运行——因为CTE b 和 a 是两个独立的子查询，b 的作用域里并没有query_rows这个标识符。之所以你觉得它能运行，大概率是两种情况：

• 你当前会话中刚好定义了同名的绑定变量query_rows
• 你实际运行的SQL其实是通过子查询显式引用了a的值，比如：

  with a as( select 100 as query_rows from dual ) ,b as ( select * from table1 where rownum=(select query_rows from a) ) select * from b
  

这种写法是合法的，因为通过子查询明确从a中获取了query_rows的值。

2. 第二个SQL报错的两个核心原因 #

你的第二个SQL触发ORA-00904，是两个问题叠加导致的：

（1）列名使用了Oracle保留字DATE #

DATE是Oracle的内置保留关键字，如果你表中的列名确实是date，必须用双引号"把它括起来，否则Oracle会把它当成关键字解析，打乱整个SQL的语法结构，进而导致后面的query_date被错误识别为无效标识符。

（2）CTE之间未显式关联，无法直接引用其他CTE的列 #

和第一个SQL的问题本质一样，b子查询无法直接访问a中的query_date，必须通过子查询或者表关联操作来获取这个值。

修正后的正确写法 #

把两个问题都修复后，SQL可以写成这两种形式：

方式一：子查询引用CTE值

with a as( select DATE '2020-10-01' as query_date from dual ) 
,b as ( 
    select t.* 
    from table1 t
    where t."DATE" = (select query_date from a)
) 
select * from b;

方式二：交叉关联CTE

with a as( select DATE '2020-10-01' as query_date from dual ) 
,b as ( 
    select t.* 
    from table1 t
    cross join a
    where t."DATE" = a.query_date
) 
select * from b;

额外小建议 #

• 永远避免用Oracle的保留关键字作为表名、列名，这会带来很多不必要的语法麻烦
• 在CTE中引用其他CTE的列时，一定要通过显式子查询或者表关联的方式，不要依赖“隐式识别”——Oracle不支持这种写法，还会降低SQL可读性

内容的提问来源于stack exchange，提问作者Dan