ORA-3099:此处不允许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联接语法，而不是Oracle的旧式联接。

db<gt；篡改一些疯狂的数据。