替换旧式外连接为ANSI左连接遇ORA-25156及结果不一致问题

问题背景 #

需要在查询中引入PER_ALL_ASSIGNMENTS_F表的Assignment_type字段，但原SQL混合使用旧式(+)外连接和ANSI左连接时触发ORA-25156: old style outer join (+) cannot be used with ANSI joins错误；将所有连接改为ANSI格式后，查询结果与原SQL不一致。

问题根源 #

1. 原SQL混用两种连接语法，违反Oracle语法规则
2. 原SQL中PER_ALL_ASSIGNMENTS_F的关联字段写错（LADTY应为LADTBY），且过滤条件放在WHERE子句中，导致左连接逻辑失效，实际变为内连接
3. 原SQL中对PER_ALL_ASSIGNMENTS_F的过滤条件未明确关联主表的生效逻辑

修正后的完整SQL #

SELECT
    'XX' KEY,
    EE.RECEIPT_MISSING_FLAG,
    EE.RECEIPT_REQUIRED_FLAG,
    EE.RECEIPT_TIME,
    EET.NAME EXPENSE_TYPE,
    EE.EXPENSE_TYPE_CATEGORY_CODE,
    PREP.PERSON_NUMBER PREPARER_PERSON_NBR,
    EER.EXPENSE_REPORT_TOTAL,
    EE.REIMBURSABLE_AMOUNT,
    EE.RECEIPT_AMOUNT,
    EE.EXPENSE_ID,
    EE.EMP_DEFAULT_COST_CENTER,
    EE.NUMBER_OF_ATTENDEES,
    replace(replace(replace(replace(replace(replace(replace(EE.DESCRIPTION,chr(9),''),chr(10),''),chr(11),''),chr(12),''),chr(13),''),chr(124),''),chr(34),'') EXPENSE_DESCR,
    replace(replace(replace(replace(replace(replace(replace(EER.PURPOSE,chr(9),''),chr(10),''),chr(11),''),chr(12),''),chr(13),''),chr(124),''),chr(34),'') EXP_PURPOSE,
    EER.EXPENSE_REPORT_NUM EXPENSE_REPORT_NUM,
    PER.PERSON_NUMBER PERSON_NBR,
    to_char(EER.EXPENSE_REPORT_DATE,'YYYY-MM-DD') EXP_REPORT_DATE,
    CAPPR.PERSON_NUMBER CURRENT_APPR_PERSON_NBR,
    to_char(EER.FINAL_APPROVAL_DATE,'YYYY-MM-DD') EXP_APPROVAL_DATE,
    HOUFL.NAME BUSINESS_UNIT,
    EER.AUDIT_CODE,
    LADTBY.PERSON_NUMBER LAST_AUDIT_PERSON_NBR,
    EER.AUDIT_RETURN_REASON_CODE,
    EER.AUDIT_PRIOR_MGR_STATUS_CODE,
    replace(replace(replace(replace(replace(replace(replace(EE.JUSTIFICATION,chr(9),''),chr(10),''),chr(11),''),chr(12),''),chr(13),''),chr(124),''),chr(34),'') EXP_JUSTIFICATION,
    to_char(EE.START_DATE,'YYYY-MM-DD') EXP_START_DATE,
    EE.TIP_AMOUNT,
    EE.EXPENSE_SOURCE,
    EE.MERCHANT_NAME,
    EE.VEHICLE_CATEGORY_CODE,
    EE.VEHICLE_TYPE,
    EE.DAILY_DISTANCE,
    EE.DISTANCE_UNIT_CODE,
    EE.TRIP_DISTANCE,
    EE.TICKET_CLASS_CODE,
    EE.TICKET_NUMBER,
    EE.FLIGHT_NUMBER,
    EE.RANGE_LOW,
    EE.RANGE_HIGH,
    EE.LOCATION,
    EER.EXPENSE_STATUS_CODE,
    (CASE
        WHEN ASS.ASSIGNMENT_TYPE = 'E' THEN 'Employee'
        WHEN ASS.ASSIGNMENT_TYPE = 'C' THEN 'Contractor'
        WHEN ASS.ASSIGNMENT_TYPE = 'N' THEN 'Nonworker'
        WHEN ASS.ASSIGNMENT_TYPE = 'P' THEN 'Pending'
        WHEN ASS.ASSIGNMENT_TYPE = 'O' THEN 'Offered'
        ELSE TO_CHAR(ASS.ASSIGNMENT_TYPE)
    END) ASSIGNMENT_TYPE,
    EE.DESTINATION_FROM DESTINATION_FROM, 
    EE.DESTINATION_TO DESTINATION_TO,
    to_char(EE.START_DATE,'YYYY-MM-DD') TRANSACTION_DATE
FROM EXM_EXPENSES EE
INNER JOIN EXM_EXPENSE_REPORTS EER ON EE.EXPENSE_REPORT_ID = EER.EXPENSE_REPORT_ID
LEFT JOIN PER_ALL_PEOPLE_F PER 
    ON EER.PERSON_ID = PER.PERSON_ID
    AND TRUNC(SYSDATE) BETWEEN TRUNC(PER.EFFECTIVE_START_DATE) AND TRUNC(PER.EFFECTIVE_END_DATE)
LEFT JOIN HR_ORGANIZATION_UNITS_F_TL HOUFL 
    ON EER.ORG_ID = HOUFL.ORGANIZATION_ID
    AND HOUFL.LANGUAGE = 'US'
    AND TRUNC(SYSDATE) BETWEEN TRUNC(HOUFL.EFFECTIVE_START_DATE) AND TRUNC(HOUFL.EFFECTIVE_END_DATE)
LEFT JOIN PER_ALL_PEOPLE_F PREP 
    ON EE.PREPARER_ID = PREP.PERSON_ID
    AND TRUNC(SYSDATE) BETWEEN TRUNC(PREP.EFFECTIVE_START_DATE) AND TRUNC(PREP.EFFECTIVE_END_DATE)
INNER JOIN EXM_EXPENSE_TYPES EET ON EE.EXPENSE_TYPE_ID = EET.EXPENSE_TYPE_ID
LEFT JOIN PER_ALL_PEOPLE_F CAPPR 
    ON EER.CURRENT_APPROVER_ID = CAPPR.PERSON_ID
    AND TRUNC(SYSDATE) BETWEEN TRUNC(CAPPR.EFFECTIVE_START_DATE) AND TRUNC(CAPPR.EFFECTIVE_END_DATE)
LEFT JOIN PER_ALL_PEOPLE_F LADTBY 
    ON EER.LAST_AUDIT_BY = LADTBY.PERSON_ID
    AND TRUNC(SYSDATE) BETWEEN TRUNC(LADTBY.EFFECTIVE_START_DATE) AND TRUNC(LADTBY.EFFECTIVE_END_DATE)
LEFT JOIN PER_ALL_ASSIGNMENTS_F ASS 
    ON PER.PERSON_ID = ASS.PERSON_ID
    AND ASS.PRIMARY_FLAG = 'Y'
    AND ASS.EFFECTIVE_END_DATE > SYSDATE
    AND ASS.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
    AND ASS.ASSIGNMENT_TYPE IN ('E', 'C', 'N', 'P', 'O')
WHERE EER.EXPENSE_REPORT_DATE BETWEEN NVL(:START_DATE, TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE, -2)) + 1)) 
                                  AND NVL(:END_DATE, LAST_DAY(ADD_MONTHS(SYSDATE, -1)))
ORDER BY EER.EXPENSE_REPORT_NUM

关键修改说明 #

1. 统一连接语法：将所有旧式(+)外连接替换为ANSI LEFT JOIN，避免语法冲突
2. 修正关联错误：将原SQL中错误的LADTY改为LADTBY，确保连接逻辑正确
3. 调整过滤条件位置：将PER_ALL_ASSIGNMENTS_F的过滤条件移至ON子句中，保留左连接语义（若需仅保留有匹配分配记录的行，可将LEFT JOIN改为INNER JOIN并将过滤条件移回WHERE）
4. 明确生效日期逻辑：所有基于_F表的生效日期过滤都放在ON子句中，确保只关联当前有效的记录
5. 字段前缀明确：在CASE表达式中添加ASS.前缀，避免字段歧义

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