时间戳

首页 > 解决方案 > ORA-30929: 此处不允许使用 ORDER SIBLINGS BY 子句

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

问题描述

我正在尝试编写一个 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

标签： sqldatabaseoraclesql-order-byhierarchical-data

解决方案

可以先做join，再对join的结果进行层次查询：

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<> 摆弄一些疯狂的数据。

推荐阅读