原始查询语句 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
我不明白为什么其他字段没这个问题,删除别名或者删除分页又能正常执行
1
zqf01 2023-08-28 22:03:14 +08:00 via Android
我记得 AS 后面应该直接跟字段别名,ORACLE 的别名可以这样写吗?
|