Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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 
  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 (
     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 
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          
WHERE 1=1
ORDER BY
Month
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 
 AND LEVEL_ALIAS = 1 + MONTHS_BETWEEN(TRUNC(EndDate,'MM'),TRUNC(StartDate,'MM'))
FROM (
   SELECT DISTINCT 
     LEVEL as LEVEL_ALIAS,
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.
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.