Oracle Query not giving result though outer join is present

Question

In the below query when i comment one line AND EVT.EVT_EVNT_ID(+) = DPT.DPT_EVNT_ID or add outer join (+) in one line AND EVT.EVT_ENTITY_ID ='G' the query will fetch results, else it will not give any results. `

select *         from 
    (select B11.ext_db_id,B11.FRST_LINE_OF_PRFRD_NAME, E11.*, D1.D1_evnt_id,    
        (SELECT E1.E1_DT
           FROM  S1core.E1_EVNT_DT_DTLS E1
          WHERE E1.E1_ENTITY_ID = D1.D1_ENTITY_ID
          AND E1.E1_EVNT_ID     = D1.D1_EVNT_ID
          AND E1.E1_D1_ID      = D1.D1_D1_ID
          AND E1.E1_DT_TYP      = 'MEET'
          AND E1.E1_OPTN_SEQ_N  = '999'
          AND E1.STATUS          = 'AUTHD' ) "MEET_DATE"
         FROM  S1core.E1_EVNT_DT_DTLS E11,  S1core.SECURITY_ACCOUNT SFA,
         S1core.EXTRNL_SYS_DETAILS EXT,
         S1core.D1_DPOT_EVNT_DTLS D1,  S1core.IP_ACNT_RELATION i1,
         S1core.ELG_ELGBLTY E1,S1core.P1_D1_PRXY_DTLS P1, S1core.EVT_DTLS   EVT,
         S1core.BUSINESS_PARTNER B1, S1core.BUSINESS_PARTNER B11
        WHERE
        P1.P1_EVNT_ID(+)     = D1.D1_EVNT_ID                   
        AND B1.BP_ID          = i1.IP_ID
        AND SFA.BP_ID          = B11.BP_ID
        AND B11.OWNER_ENTITY  =  SFA.OWNER_ENTITY
        AND P1.P1_D1_ID(+)   = D1.D1_D1_ID
       AND P1.P1_ENTITY_ID(+) = D1.D1_ENTITY_ID
        AND P1.STATUS(+)        = 'AUTHD'
        AND EXT.LVL_REF    =    SFA.SCA_REF
        AND EXT.OWNER_ENTITY  =  SFA.owner_entity
        AND EXT.EXTRNL_SYS_ID  = '39'
        AND EXT.BP_ID = SFA.BP_ID
        AND EXT.LVL = 5
        AND E1.ELG_SEC_ID            = D1.D1_SEC_ID
        AND D1.D1_ENTITY_ID        = E1.ELG_ENTITY_ID
        AND D1.D1_EVNT_ID          = E1.ELG_EVNT_ID
        AND D1.D1_D1_ID           = E1.ELG_D1_ID
        AND E1.ELG_ENTITY_ID         = SFA.OWNER_ENTITY
        AND E1.ELG_ACNT_ID           = SFA.SCA_REF
        AND i1.owner_entity        = SFA.OWNER_ENTITY
        AND i1.owner_entity        = B1.OWNER_ENTITY
        AND i1.SCA_REF  = SFA.SCA_REF
        AND i1.stat              <> 2
        AND EVT.EVT_ENTITY_ID      ='G'
        AND EVT.EVT_EVNT_ID(+)       = D1.D1_EVNT_ID
        AND EVT.STATUS(+)            = 'AUTHD'
        AND E11.E1_ENTITY_ID       = D1.D1_ENTITY_ID
        AND E11.E1_EVNT_ID         = D1.D1_EVNT_ID
        AND E11.E1_D1_ID          = D1.D1_D1_ID
        AND D1.D1_EVNT_GRP         = 'MEETING'
        AND E1.ELG_FNL_ELGBL_QNTTY   > 0
        AND D1.D1_ENTITY_ID        = 'GSSIN' -- P_D1_ENTITY_ID
        AND B11.ext_db_id LIKE 'LICMF' -- P_GLOBAL_CUSTODIAN
         AND B1.ext_db_id LIKE '%' -- P_FUND_MANAGER
         AND SUBSTR(EXT.LEVEL_EXTNL_SYS,1,5) LIKE '%' -- P_SUB_ACCOUNT_ID
         AND SUBSTR(EXT.LEVEL_EXTNL_SYS,6,9) LIKE '%' -- P_SCHEMA_ID
         AND SUBSTR( D1.D1_EVNT_TYP, 1, 4 ) LIKE '%' -- P_EVENT_TYPE
        AND E11.E1_DT_TYP = 'MEET'
        and D1.D1_evnt_id='12457886544'
       AND D1.OU_ID  IN('17','80') -- ('S1 INDIA PC')
       ) ;

Am I missing any outer join or the query is having any other issue as i can see that dpt_evnt_id='12457886544'is available in all tables.

`

I tried to run this query by commenting AND EVT.EVT_EVNT_ID(+) = DPT.DPT_EVNT_ID and by adding outer join in EVT.EVT_ENTITY_ID (+) ='G'

Answer 1

You have:

AND EVT.EVT_ENTITY_ID  ='G'
AND EVT.EVT_EVNT_ID(+) = D1.D1_EVNT_ID
AND EVT.STATUS(+)      = 'AUTHD'

Even though the second two have (+) the first one does not so the join to EVT will be treated as an INNER JOIN and not an OUTER JOIN.

You should convert your code to use ANSI joins as it will be much easier to spot issues like this.

Note: There may be more issues like this. However, your code has so many joins, the syntax is archaic, and we have no way of validating the correctness that I stopped looking after finding the first issue. You are going to need to debug it all to check for any other issues.