Oracle - 查询块需要使用Connect By子句

Question

我希望在Case语句中使用下面语法中的月份列。当我创建子查询时，会收到Oracle错误01788 Connect By Clause Required in query block。如何在子查询中使用Month列在Case语句中？

TO_CHAR(ADD_MONTHS(TRUNC(StartDate, 'MM'), LEVEL - 1), 'YYYYMM') AS Month

以下是查询语句：

    SELECT 
CASE 
  WHEN  first_assgn_dt_YYYYMM <= Month 
   THEN 0
  WHEN EndDate < LAST_DAY(EndDate) AND EndDate != sysdate 
 AND LEVEL = 1 + MONTHS_BETWEEN(TRUNC(EndDate,'MM'),TRUNC(StartDate,'MM'))
  THEN 0 
    ELSE 1 
      END AS active_at_month_end 

        FROM (
WITH
     ActiveMemberData (ID,StartDate,EndDate,first_assgn_dt,first_assgn_dt_YYYYMM) AS (
       SELECT DISTINCT 
       x.ID,
       TRUNC(x.start_dt) AS StartDate,
       CASE WHEN TRUNC(X.END_DT) = '1-JAN-3000' THEN SYSDATE ELSE TO_DATE(X.END_DT) END AS EndDate,
       x.first_assgn_dt,
       TO_CHAR(first_assgn_dt,'YYYYMM') AS first_assgn_dt_YYYYMM

FROM X
LEFT JOIN  D ON X.MID = D.ID  

WHERE 1=1

                               )                                   
--------------------------------------------------                                       
   SELECT DISTINCT 
ID,
first_assgn_dt,
first_assgn_dt_YYYYMM,
StartDate,
TO_CHAR(StartDate,'YYYYMM') AS StartDate_YYYYMM, 
EndDate,
TO_CHAR(ADD_MONTHS(TRUNC(StartDate, 'MM'), LEVEL - 1), 'YYYYMM') AS Month,
LAST_DAY(EndDate) AS LastDayOfMonth

FROM  ActiveMemberData

WHERE 1=1
------------------------------------------------------------------------------------
CONNECT BY LEVEL <= 1 + MONTHS_BETWEEN(TRUNC(EndDate,'MM'), TRUNC(StartDate,'MM'))
  AND PRIOR ID = ID AND PRIOR STARTDATE = STARTDATE
  AND PRIOR sys_guid() IS NOT NULL          
                       ) Z
WHERE 1=1

ORDER BY
ID, 
Month

- Toby

Answer 1

这与尝试从内联视图引用Month无关，那是没有问题的。导致错误的是对level的单独引用。

如果您想要在外部查询中查看来自内联视图的level，就像您使用此行代码一样：

 AND LEVEL = 1 + MONTHS_BETWEEN(TRUNC(EndDate,'MM'),TRUNC(StartDate,'MM'))

然后你需要在选择列表中包含它 - 使用别名 - 然后引用该别名：

SELECT 
CASE 
...
 AND LEVEL_ALIAS = 1 + MONTHS_BETWEEN(TRUNC(EndDate,'MM'),TRUNC(StartDate,'MM'))
...
FROM (
...
   SELECT DISTINCT 
     LEVEL as LEVEL_ALIAS,
     ID,
...

当然，您可以随意命名别名；但是您不能使用保留字level。

任何您想在外部查询中看到的内容都必须在内联视图的选择列表中 - 但通常您可以保留原始列名称；但对于表达式或伪列，您必须使用别名，这就是此处的情况。

- Alex Poole

Answer 2

您不必为保留字使用别名。只需添加双引号并将其大写，即 "LEVEL"。

- cogeo