Oracle SQL中WITH子查询列person_id识别异常问题咨询

问题场景 #

使用WITH子句定义了名为person_type的子查询作为表使用，但保留关联条件person_type.person.id = A.person_id时，数据库提示person_type.person_id invalid identifier（无效标识符）；移除该条件后，查询可正常运行，且能识别person_type中的effective_start_date和effective_end_date列。

原始代码 #

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

问题原因 #

核心问题是字段名书写错误：

• 查看person_type子查询的SELECT列表，仅返回了user_person_type、person_id、effective_start_date、effective_end_date四个字段，不存在person.id这个嵌套字段（子查询里没有名为person的表别名，也未选中该表的id字段）。
• 你在WHERE条件里误将person_type.person_id写成了person_type.person.id，数据库无法识别这个不存在的字段，因此抛出无效标识符错误。
• 移除该错误条件后，剩余条件使用的都是person_type子查询中实际存在的字段，所以查询能正常执行。

解决方案 #

将WHERE条件中的错误字段名修正为子查询中实际存在的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 ...

内容的提问来源于stack exchange，提问作者Aasem Shoshari