欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

ORA-01791:不是SElECTed表达式

程序员文章站 2022-06-11 17:07:15
...
SELECT NVL(T11.DEPID, 'NULL'),
       NVL(T11.PARDEPID, 'NULL'),
       NVL(T11.DEPNAME, 'NULL')
  FROM (SELECT DISTINCT DEPID,
                        PARDEPID,
                        DEPNAME,
                        DEPCODE,
                        SPECIALTYCODE,
                        ROTAID
          FROM SYS_DEPARTMENT
         START WITH DEPID IN
                    (SELECT DEPID
                       FROM SYS_SM_ROLEDEP
                      WHERE ROLEID IN (SELECT ROLEID
                                         FROM SYS_USERQUARTERS
                                        WHERE USERID = '1'))
        CONNECT BY PARDEPID = PRIOR DEPID
         ORDER BY ORDERNO) T11

   今天在数据库执行以上代码的时候 出现“ORA-01791:不是SElECTed表达式”这个错误

   经分析,如果在ORDER BY中指定多个列,结果将先按照子句中的第一个列排序,然后第二个,依此类推
在 SELECT中未出现的列名也可用于ORDER BY 子句中,主要TABLE中有就行,但如果SELECT子句中出现了DISTINCT关键字,则只能用出现过的列名,而且如果SELECT子句中使用了任何运算符,在ORDER BY 子句中必须保持和SELECT子句中表达式完全一致,否则出现ORA-01791 ERROR

   所以将对应字段添加上就可以了

SELECT NVL(T11.DEPID, 'NULL'),
       NVL(T11.PARDEPID, 'NULL'),
       NVL(T11.DEPNAME, 'NULL')
  FROM (SELECT DISTINCT DEPID,
                        PARDEPID,
                        DEPNAME,
                        DEPCODE,
                        SPECIALTYCODE,
                        ROTAID,
                        ORDERNO
          FROM SYS_DEPARTMENT
         START WITH DEPID IN
                    (SELECT DEPID
                       FROM SYS_SM_ROLEDEP
                      WHERE ROLEID IN (SELECT ROLEID
                                         FROM SYS_USERQUARTERS
                                        WHERE USERID = '1'))
        CONNECT BY PARDEPID = PRIOR DEPID
         ORDER BY ORDERNO) T11

 

 

相关标签: sql distinct