Self/Outer Join

From: Jill <jc_va_at_hotmail.com>
Date: Sun, 10 Oct 1999 19:45:20 -0700
Message-ID: <7tr8gl$97$1@bgtnsc02.worldnet.att.net>

I am inserting payroll data from 2 databases into a single table for parallel test comparison purposes. I do self join for where the person and earnings code exist in both, with a union to 2 outer-self joins in case they exist in one database and not the other. However, the outer/self joins don't work - they don't return any rows. When I do a "NOT EXISTS" it works fine. This is not a problem, but I won't be able to sleep until I understand why the outer join won't work.

The table is not indexed nor has any constraints.

Thanks for any ideas.

Good code:

...
...
UNION ALL
SELECT

 P.NAME

,P.EMPLID
,P.ERNCD
,0 AS TST_HRS
,0. AS TST_PAY
,0. AS TST_ERN
,P.OTH_HRS AS PRD_HRS
,P.OTH_PAY AS PRD_PAY
,P.OTH_EARNS AS PRD_ERN

FROM PR_COMP P
WHERE P.GLOBAL_NAME = 'PROD.WORLD'
AND NOT EXISTS (SELECT 'X'

                FROM PR_COMP T
                WHERE T.EMPLID = P.EMPLID
                AND   T.ERNCD = P.ERNCD
                AND   T.GLOBAL_NAME = 'TEST.WORLD')

Does not work below...

...
...
UNION ALL
SELECT

 P.NAME

,P.EMPLID
,P.ERNCD
,0 AS TST_HRS
,0. AS TST_PAY
,0. AS TST_ERN
,P.OTH_HRS AS PRD_HRS
,P.OTH_PAY AS PRD_PAY
,P.OTH_EARNS AS PRD_ERN

FROM PR_COMP P
    ,PR_COMP T
WHERE P.GLOBAL_NAME = 'PROD.WORLD'

AND P.EMPLID = T.EMPLID (+)
AND P.ERNCD = T.ERNCD   (+)
AND T.EMPLID IS NULL

Received on Sun Oct 10 1999 - 21:45:20 CDT