英文:
Oracle SQL grand query does not recognize column selected in with clause
问题
我有一个子查询,使用with
子句作为表,奇怪的是,如果我删除条件person_type.person.id = A.person_id
,它可以正常工作,因此它会识别person_type.effective_start_date
和person_type.effective_end_date
来自person_type
的with
子句,但不会识别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'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?
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论