V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
coderstory
V2EX  ›  数据库

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

  •  
  •   coderstory · 2023-08-28 15:26:27 +08:00 · 883 次点击
    这是一个创建于 430 天前的主题,其中的信息可能已经有所发展或是发生改变。

    原始查询语句 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 条回复    2023-08-28 22:03:14 +08:00
    zqf01
        1
    zqf01  
       2023-08-28 22:03:14 +08:00 via Android
    我记得 AS 后面应该直接跟字段别名,ORACLE 的别名可以这样写吗?
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   1424 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 21ms · UTC 23:51 · PVG 07:51 · LAX 16:51 · JFK 19:51
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.