Can you please let me know what are the possible reasons why the
sub-query SOMETIMES does not work with outer join..



Here is the example...





If I use the following statement..

(Subquery No1) Y LEFT OUTER JOIN
(SELECT ORG.COMPANY

, ORG.GROUP

, SEL.CUST_ID

FROM PS_INTFC_SEL ISEL

, PS_SEL SEL

, PS_SEL_ORG ORG

WHERE ISEL.P_ID = 'MMI'

AND SEL.P_ID = ISEL.P_ID

AND SEL.P_SEL_ID = ISEL.P_SEL_ID

AND ORG.P_ID = SEL.P_ID

AND ORG.P_SEL_ID = SEL.P_SEL_ID) X ON X.COMPANY = Y.COMPANY AND
X.GROUP = Y.GROUP
AND Y.EMPL_STATUS IN ('A', 'Z', 'S', 'L', 'O', 'P', 'N', 'B') WHERE
Y.BEGIN_DT <= TO_DATE('2007-05-31','YYYY-MM-DD')




But if I create a table :

Crate table PS_COMP_CUST_MMI_TBL as
(



SELECT ORG.COMPANY

, ORG.GROUP

, SEL.CUST_ID

FROM PS_INTFC_SEL ISEL

, PS_SEL SEL

, PS_SEL_ORG ORG

WHERE ISEL.P_ID = 'MMI'

AND SEL.P_ID = ISEL.P_ID

AND SEL.P_SEL_ID = ISEL.P_SEL_ID

AND ORG.P_ID = SEL.P_ID

AND ORG.P_SEL_ID = SEL.P_SEL_ID)



AND if I this table instead of subquery:





(Subquery No1) Y LEFT OUTER JOIN
PS_COMP_CUST_MMI_TBL X ON X.COMPANY = Y.COMPANY AND X.GROUP = Y.GROUP
AND Y.EMPL_STATUS IN ('A', 'Z', 'S', 'L', 'O', 'P', 'N', 'B') WHERE
Y.BEGIN_DT <= TO_DATE('2007-05-31','YYYY-MM-DD')




Then I receive completely different results?!





Regards



Mirsada




Oracle LazyDBA home page