ORACLE 有个很迷的查询 删除别名或者分页后就能执行

[coderstory]

原始查询语句 ORACLE19c V_PU_FBHT 是一个视图 具体定义不明 没权限看

SELECT
	"V_PU_FBHT"."Z_FBHTBH" AS "[V_PU_FBHT].Z_FBHTBH",
	"V_PU_FBHT"."Z_FBHTCWHSH" AS "[V_PU_FBHT].Z_FBHTCWHSH",
	"V_PU_FBHT"."Z_YXHTBH" AS "[V_PU_FBHT].Z_YXHTBH",
	"V_PU_FBHT"."Z_YXHTMC" AS "[V_PU_FBHT].Z_YXHTMC",
	"V_PU_FBHT"."UGENPROJECTNUMBER" AS "[V_PU_FBHT].UGENPROJECTNUMBER",
	"V_PU_FBHT"."Z_YXHTCWHSH" AS "[V_PU_FBHT].Z_YXHTCWHSH",
	"V_PU_FBHT"."Z_YFMC" AS "[V_PU_FBHT].Z_YFMC",
	"V_PU_FBHT"."Z_FBHTMC" AS "[V_PU_FBHT].Z_FBHTMC",
	"V_PU_FBHT"."Z_XCWHSH" AS "[V_PU_FBHT].Z_XCWHSH",
	"V_PU_FBHT"."UUU_RECORD_LAST_UPDATE_DATE" AS "[V_PU_FBHT].UUU_RECORD_LAST_UPDATE_DATE",
	"V_PU_FBHT"."UGENPROJECTNAME" AS "[V_PU_FBHT].UGENPROJECTNAME" 
FROM
	"V_PU_FBHT" "V_PU_FBHT" OFFSET 0 ROW FETCH NEXT 50 ROW ONLY 

这个 SQL 执行报错

在行: 1 上开始执行命令时出错 -
SELECT
	"V_PU_FBHT"."Z_FBHTBH" AS "[V_PU_FBHT].Z_FBHTBH",
	"V_PU_FBHT"."Z_FBHTCWHSH" AS "[V_PU_FBHT].Z_FBHTCWHSH",
	"V_PU_FBHT"."Z_YXHTBH" AS "[V_PU_FBHT].Z_YXHTBH",
	"V_PU_FBHT"."Z_YXHTMC" AS "[V_PU_FBHT].Z_YXHTMC",
	"V_PU_FBHT"."UGENPROJECTNUMBER" AS "[V_PU_FBHT].UGENPROJECTNUMBER",
	"V_PU_FBHT"."Z_YXHTCWHSH" AS "[V_PU_FBHT].Z_YXHTCWHSH",
	"V_PU_FBHT"."Z_YFMC" AS "[V_PU_FBHT].Z_YFMC",
	"V_PU_FBHT"."Z_FBHTMC" AS "[V_PU_FBHT].Z_FBHTMC",
	"V_PU_FBHT"."Z_XCWHSH" AS "[V_PU_FBHT].Z_XCWHSH",
	"V_PU_FBHT"."UUU_RECORD_LAST_UPDATE_DATE" AS "[V_PU_FBHT].UUU_RECORD_LAST_UPDATE_DATE",
	"V_PU_FBHT"."UGENPROJECTNAME" AS "[V_PU_FBHT].UGENPROJECTNAME" 
FROM
	"V_PU_FBHT" "V_PU_FBHT" OFFSET 0 ROW FETCH NEXT 50 ROW ONLY
错误位于命令行: 14 列: 33
错误报告 -
SQL 错误: ORA-00904: "A1"."[V_PU_FBHT].UUU_RECORD_LAST_UPDATE_DATE": 标识符无效
ORA-02063: 紧接着 line (起自 CERIPU)
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:

结果排查，SQL 精简到如下查询

在行: 1 上开始执行命令时出错 -
SELECT  "V_PU_FBHT"."UUU_RECORD_LAST_UPDATE_DATE" AS "[V_PU_FBHT].UUU_RECORD_LAST_UPDATE_DATE"
FROM
V_PU_FBHT  "V_PU_FBHT" OFFSET 0 ROW FETCH NEXT 50 ROW ONLY
错误位于命令行: 1 列: 158
错误报告 -
SQL 错误: ORA-00904: "A1"."[V_PU_FBHT].UUU_RECORD_LAST_UPDATE_DATE": 标识符无效
ORA-02063: 紧接着 line (起自 CERIPU)
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
SQL> 

此时我把 UUU_RECORD_LAST_UPDATE_DATE 字段的别名删掉，或者去掉分页，sql 都能正常查询。

SQL> SELECT
  2                  "V_PU_FBHT"."UUU_RECORD_LAST_UPDATE_DATE" 
  3  FROM
  4   "V_PU_FBHT" 
  5* ORDER BY  "V_PU_FBHT"."UUU_RECORD_LAST_UPDATE_DATE"   OFFSET 0 ROW FETCH NEXT 50 ROW ONLY;

UUU_RECORD_LAST_UPDATE_DATE    
______________________________ 
10-9 月 -21                      
12-12 月-21                      
12-12 月-21                      
13-12 月-21  

我不明白为什么其他字段没这个问题，删除别名或者删除分页又能正常执行

[zqf01]

我记得 AS 后面应该直接跟字段别名，ORACLE 的别名可以这样写吗？