我使用with
子句将子查询作为表,奇怪的是,如果我删除条件person_type.person.id = A.person_id
,它将正常工作,因此它将从person_type
with
子句中识别person_type.effective_start_date
和person_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 ...
1条答案
按热度按时间a0x5cqrl1#
先知是对的,你犯了个错误。