Oracle - Connect By Clause Required in query block

Question

I would like to utilize the Month Column in the below syntax in a Case Statement. When I create a sub query I receive the Oracle error 01788 Connect By Clause Required in query block. How can one utilize the Month column in the case statment in the subquery?

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

Query below:

    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

Answer 1

That has nothing to do with trying to refer to Month from the inline view; that is fine. It's the separate reference to level that is causing the error.

If you want to be able to see the level from your inline view in the outer query, as you are with this line:

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

then you have to include it in the select list - with an alias - and then refer to that alias:

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

You can call the alias whatever you want, of course; you just can't use the reserved word level.

Anything you want visible in the outer query always has to be in the inline view's select list - but usually you can keep the original column name; you have to use an alias for an expression or a pseucocolumn though, which is the case here.