I can help you if you provide (test it out first) a very simplified
version of your query showing clearly the data and the query results.
Kalman
-----Original Message-----
From: gasta019
[mailto:oracledba-ezmlmshield-x52668231.[Email address protected]
Sent: Friday, May 04, 2007 1:53 PM
To: LazyDBA Discussion
Subject: isue wh qery with left outer join - oracle 9.2.0.6
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
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
------------------------------------------------------------------------------
Notice: This e-mail message, together with any attachments, contains
information of Merck & Co., Inc. (One Merck Drive, Whitehouse Station,
New Jersey, USA 08889), and/or its affiliates (which may be known
outside the United States as Merck Frosst, Merck Sharp & Dohme or MSD
and in Japan, as Banyu - direct contact information for affiliates is
available at http://www.merck.com/contact/contacts.html) that may be
confidential, proprietary copyrighted and/or legally privileged. It is
intended solely for the use of the individual or entity named on this
message. If you are not the intended recipient, and have received this
message in error, please notify us immediately by reply e-mail and then
delete it from your system.
------------------------------------------------------------------------------
Oracle LazyDBA home page