Oracle SQL大查询无法识别with子句中选定的列

s4n0splo  于 2023-03-01  发布在  Oracle
关注(0)|答案(1)|浏览(104)

我使用with子句将子查询作为表,奇怪的是,如果我删除条件person_type.person.id = A.person_id,它将正常工作,因此它将从person_typewith子句中识别person_type.effective_start_dateperson_type.effective_end_date,但不能识别person_id,因为它显示person_type.person_id invalid identifier,这里有什么问题?

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 ...
a0x5cqrl

a0x5cqrl1#

先知是对的,你犯了个错误。

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

相关问题