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

Question

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

Answer 1

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)