Oracle SQL大查询不识别在WITH子句中选择的列。

huangapple go评论66阅读模式
英文:

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

问题

我有一个子查询,使用with子句作为表,奇怪的是,如果我删除条件person_type.person.id = A.person_id,它可以正常工作,因此它会识别person_type.effective_start_dateperson_type.effective_end_date来自person_typewith子句,但不会识别person_id,因为它说person_type.person_id是无效的标识符,问题出在哪里?

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

<details>
<summary>英文:</summary>

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&#39;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 ...

答案1

得分: 0

Oracle是正确的,你犯了一个错误。

with person_type as 
  (
   SELECT pptt.user_person_type, paam.person_id, ...
  )                                         ^
                                            |
WHERE person_type.person.id = A.person_id
                        ^
                        |
                这里有什么奇怪的吗?
英文:

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?

huangapple
  • 本文由 发表于 2023年2月18日 23:40:40
  • 转载请务必保留本文链接:https://go.coder-hub.com/75494430.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定