Oracle用Dual创建虚拟表后指定列查询报ORA-00904错误求助

问题场景 #

在Oracle中用Dual表创建虚拟表时，执行SELECT * FROM DUMMY能正常返回数据，但执行SELECT id, bank FROM DUMMY时抛出错误：

sql - ORA-00904: invalid identifier

使用的SQL代码如下：

WITH
  DUMMY as 
    (   
        Select 1 "id", 'UBS' "bank" From Dual Union ALL
        Select 2, 'Credit Suisse' From Dual
    )
 -- SELECT * FROM DUMMY  it work
 SELECT id, bank FROM DUMMY -- it dont WORK, why?

问题原因 #

Oracle里，用双引号"包裹列名时，会强制区分列名的大小写。你定义虚拟表DUMMY时，给列名加了双引号，实际存储的列名是小写的id和bank。

但查询时写的SELECT id, bank FROM DUMMY，Oracle默认会把未加双引号的标识符转成大写，也就是实际会去查找ID和BANK这两个列——而你的虚拟表里根本没有这两个大写列，所以触发了ORA-00904无效标识符错误。

至于SELECT *能正常运行，是因为*会直接返回所有列，不需要指定列名，自然不会碰到大小写匹配的问题。

解决方法 #

有两种可行的解决方式：

1. 查询时给列名加上双引号，保持和定义时的大小写一致：

WITH
  DUMMY as 
    (   
        Select 1 "id", 'UBS' "bank" From Dual Union ALL
        Select 2, 'Credit Suisse' From Dual
    )
SELECT "id", "bank" FROM DUMMY;

2. 定义虚拟表时，不要用双引号包裹列名，让Oracle自动按默认规则处理为大写：

WITH
  DUMMY as 
    (   
        Select 1 id, 'UBS' bank From Dual Union ALL
        Select 2, 'Credit Suisse' From Dual
    )
SELECT id, bank FROM DUMMY;

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