ORA-30929:此处不允许ORDER SIBLINGS BY子句

Question

我正在尝试编写一个SQL来显示分层模式。我的第一次尝试很好地处理了单个数据，因为下面的SQL是有效的。

SELECT CONCAT (LPAD (' ',LEVEL*3-3), M.MODULE) MODULE,M.ABBREVIATION,M.PARENT 
FROM    MRS_CUSTOM.CL_MODULES M
CONNECT BY PRIOR M.ABBREVIATION = M.parent
START WITH M.PARENT IS NULL
ORDER SIBLINGS BY M.MODULE;

此sql将显示以下内容：

Admissions  ADMIS   
   Admissions Correspondence    ADMCO   ADMIS
   Agent Interface to Applicant Portal  ADAIAP  ADMIS
   Applicant Portal ADMP    ADMIS
      Statistics    APSTAT  ADMP
   My.Application Portal    MYAP    ADMIS
   Precedents   ADMIS_PRD   ADMIS
   Selection Process Management SPM ADMIS
Advanced Standing   ADVSTG  
   Precedents   ADVSTG_PRD  ADVSTG
Archive Module  AM  

现在我需要附加到这个

    select (CONCAT (LPAD (' ',LEVEL*3-3), M.MODULE)) 
    MODULE,M.ABBREVIATION,M.PARENT,count(distinct(RS.REPORT_ID)) as 
    NUM_REPORTS,count(distinct(MP.DB_ROLE)) as NUM_ROLES 
    from MRS_CUSTOM.CL_MODULES M, MRS_CUSTOM.CL_MODULE_PRIVS MP, MRS_CUSTOM.CL_REPORT_SPECS RS
    where M.ABBREVIATION = RS.MODULE(+) and
    M.ABBREVIATION = MP.ABBREVIATION(+)
    CONNECT BY PRIOR M.ABBREVIATION = M.parent
    START WITH M.PARENT IS NULL
    group by M.ABBREVIATION, M.MODULE, M.PARENT
   ORDER SIBLINGS BY M.MODULE;

但是我得到了错误

ORA-30929: ORDER SIBLINGS BY clause not allowed here

Answer 1

您可以先执行连接，然后对连接结果执行分层查询：

SELECT CONCAT (LPAD (' ',LEVEL*3-3), M.MODULE) MODULE, M.ABBREVIATION, M.PARENT,
  M.NUM_REPORTS, M.NUM_ROLES
FROM (
  select M.MODULE, M.ABBREVIATION, M.PARENT,
    count(distinct(RS.REPORT_ID)) as NUM_REPORTS,
    count(distinct(MP.DB_ROLE)) as NUM_ROLES 
  from MRS_CUSTOM.CL_MODULES M
  left join MRS_CUSTOM.CL_REPORT_SPECS RS
  on RS.MODULE = M.ABBREVIATION
  left join MRS_CUSTOM.CL_MODULE_PRIVS MP
  on MP.ABBREVIATION = M.ABBREVIATION
  group by M.ABBREVIATION, M.MODULE, M.PARENT
) M
CONNECT BY PRIOR M.ABBREVIATION = M.parent
START WITH M.PARENT IS NULL
ORDER SIBLINGS BY M.MODULE;

我已经切换到ANSI join语法，而不是Oracle的旧式join。

带有一些乱码数据的db<>fiddle。