Oracle XMLTable errors with invalid identifier

Question

What is causing the error: ORA-00904: "DR"."DLR_KEY": invalid identifier? Oracle Version 12.1.0.2.0

If I comment out the 'left outer join user_dealer...' line then the query runs successfully.

select dr.dlr_key,
    dr.dlr_reference_num,
    dr.dlr_name
from dealer dr,
    XMLTable('/DLR' 
        PASSING dr.DLR_DEALER_DATA 
        COLUMNS 
        DlrMiscPymtPlan varchar(100) PATH 'DlrMiscPymtPlan'
        ) as a2
inner join wuser wu on wu.wu_key = 1
left outer join user_dealer ud on ud.udl_dlr_fkey = dr.dlr_key
    and ud.udl_usr_fkey = 1
where (
        UPPER(DLR_NAME) like 'MLB%'
        and (UPPER(a2.DlrMiscPymtPlan) like 'P')
        )

ogglethorp ogglethorp · Accepted Answer · 2017-05-18T18:14:46

The issue deals with how the XMLTable implicitly joins. This query works:

select dr.dlr_key,
    dr.dlr_reference_num,
    dr.dlr_name
from dealer dr left outer join
    XMLTable('/DLR' 
        PASSING dr.DLR_DEALER_DATA 
        COLUMNS 
        DlrMiscPymtPlan varchar(100) PATH 'DlrMiscPymtPlan'
        ) as a2 on 1=1
inner join wuser wu on wu.wu_key = 1
left outer join user_dealer ud on ud.udl_dlr_fkey = dr.dlr_key
    and ud.udl_usr_fkey = 1
where (
        UPPER(DLR_NAME) like 'MLB%'
        and (UPPER(a2.DlrMiscPymtPlan) like 'P')
        )

Thanks to information on sites: Using join with xmltable and xmltable with left outer join

Oracle XMLTable errors with invalid identifier

1 Answers