ORA-01791:不是SElECTed表达式

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