sqloracle-databasewith-clause

Oracle SQL grand query does not recognize column selected in with clause


I have sub query as table using with clause, the weird thing is that if I remove condition person_type.person.id = A.person_id it works fine, so it will recognize person_type.effective_start_date and person_type.effective_end_date from person_type with clause, but not person_id as it says person_type.person_id invalid identifier, what's the issue here?

 with person_type as (
       SELECT pptt.user_person_type, paam.person_id, paam.effective_start_date,paam.effective_end_date
        FROM fusion.PER_PERSON_TYPES      ppt,
             fusion.PER_PERSON_TYPES_TL   pptt,
             fusion.per_all_assignments_m paam
       WHERE 1 = 1
         AND ppt.person_type_id = pptt.person_type_id
         AND pptt.language = USERENV('LANG')
         AND ppt.person_type_id = paam.person_type_id
         AND paam.assignment_type = 'E'
         AND PAAM.EFFECTIVE_LATEST_CHANGE = 'Y'
         AND PAAM.Assignment_Status_Type = 'ACTIVE'
         AND paam.primary_assignment_flag = 'Y'

 )

SELECT .... 


FROM (SELECT ... FROM ... WHERE ...) A,
person_type 

WHERE person_type.person.id = A.person_id
AND TRUNC(A.date_earned) BETWEEN person_type.effective_start_date AND person_type.effective_end_date
AND ...

Solution

  • Oracle is right, you've made a mistake.

    with person_type as 
      (
       SELECT pptt.user_person_type, paam.person_id, ...
      )                                         ^
                                                |
    WHERE person_type.person.id = A.person_id
                            ^
                            |
                    see anything strange here?