我希望在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