ORA-01799: a column may not be outer-joined to a subquery-CodePudding

I have below query that returns me error message "ORA-01799: a column may not be outer-joined to a subquery”， how do I make it right? Thanks in advance for your help!

delete from TRN_HDRF_DBF where M_REFERENCE in (
select F.M_REFERENCE
    from TRN_HDRF_DBF F
  join TRN_HDR_DBF T
    on F.M_NB = T.M_NB
  join RT_LOAN_DBF I
    on T.M_NB = I.M_NB
  left outer join EVT_IMP_DBF IMP
    on I.M_NB = IMP.M_BO 
        and F.M_EVT_REF = IMP.M_EVT  
        and (select TRN_PFLD_DBF.M_LABEL from TRN_PFLD_DBF where TRN_PFLD_DBF.M_REF = F.M_SRC_PFOLIO) = IMP.M_SOURCE  
  where T.M_TRN_GTYPE = 5 
    and (I.M_STL_DAT - T.M_TRN_DATE ) > 6   
    and IMP.M_DATE < I.M_INIT_DATE and IMP.M_DATE < I.M_STL_DAT 
    and F.M_CURRENCY = I.M_STL_CUR 
    )

the query was a Sybase query and I'm trying to adapt it for Oracle

CodePudding user response：

Try putting the subquery in a CTE (without the where) and add a left outer join on it on both conditions: the original one of the where and the join one.

CodePudding user response：

Oracle doesn't like the subquery in the left outer join conditions at line 11. Try this instead:

delete from TRN_HDRF_DBF where M_REFERENCE in (

with trythis as (
   select TRN_PFLD_DBF.M_LABEL, TRN_PFLD_DBF.M_REF
   from TRN_PFLD_DBF 
   where TRN_PFLD_DBF.M_REF = F.M_SRC_PFOLIO
   )
   
select F.M_REFERENCE
  from TRN_HDRF_DBF F
  join TRN_HDR_DBF T
    on F.M_NB = T.M_NB
  join RT_LOAN_DBF I
    on T.M_NB = I.M_NB
  left join trythis tt
     on tt.M_REF = F.M_SRC_PFOLIO
  left outer join EVT_IMP_DBF IMP
    on I.M_NB = IMP.M_BO 
        and F.M_EVT_REF = IMP.M_EVT  
        and IMP.M_SOURCE = tt.M_LABEL
  where T.M_TRN_GTYPE = 5 
    and (I.M_STL_DAT - T.M_TRN_DATE ) > 6   
    and IMP.M_DATE < I.M_INIT_DATE and IMP.M_DATE < I.M_STL_DAT 
    and F.M_CURRENCY = I.M_STL_CUR 
    )
    ```

CodePudding user response：

Depending on the intended join/search criteria, perhaps replace with an exists(), eg:

left outer join EVT_IMP_DBF IMP
    on I.M_NB = IMP.M_BO 
        and F.M_EVT_REF = IMP.M_EVT  
        and exists (select 1 
                    from  TRN_PFLD_DBF
                    where TRN_PFLD_DBF.M_REF = F.M_SRC_PFOLIO
                    and   TRN_PFLD_DBF.M_LABEL = IMP.M_SOURCE)