ORA-01791:不是SElECTed表达式

最新推荐文章于 2023-03-28 11:07:40 发布

原创最新推荐文章于 2023-03-28 11:07:40 发布 · 202 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #distinct

Oracle 专栏收录该内容

3 篇文章

订阅专栏

本文详细阐述了在SQL查询中使用ORDER BY与DISTINCT关键字时遇到ORA-01791错误的原因及解决方法。通过实例分析，展示了如何在查询中正确地结合使用这些关键字，避免错误发生。

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