Outer Joins Not Working

If you are having problem getting outer joins to work, it may be due to nested effective date logic. Here is a link to a blog post that helped me.

I have also had success using a UNION.

I basically UNION the first query to a second, not exists query, to return the full record set.

For example:
I want to find the value for mail drop on the position_data table. The customer stored this data historically on a custom field, cust_mail_stop on the department table. When they decided to go with full position management, they wanted to get rid of the custom field but update position_data.mail_drop with the correct value from the department table. All history rows needed to be updated. I joined position_data.deptid to dept_tbl.deptid. Also, I had to make sure I got the appropriate effective dated row. I did this by making the dept_tbl.effdt <= position_data.effdt.

The total number of rows in position_data = 35,000
Total number of rows returned by first query = 27,000
Total number of rows returned by second, not exists query = 8,000

Query 1 - Returned 27,000 rows

SELECT a.POSITION_NBR, a.EFFDT, a.deptid, a.mail_drop, a.qvc_mail_stop AS "Pos MP", b.cust_mail_stop
FROM psoft.ps_position_data a, psoft.ps_dept_tbl b
WHERE a.deptid(+) = b.deptid
AND b.effdt = (SELECT MAX(b2.effdt)
FROM psoft.ps_dept_tbl b2
WHERE b2.setid = b.setid
AND b2.deptid = b.deptid
AND b2.effdt <= a.effdt)

The second query is what I used to find the difference between the number of rows in position_data that didn't exist in Query 1.

Query 2 - Returned 8,000 rows

SELECT p.POSITION_NBR, p.EFFDT, p.deptid, p.mail_drop, p.cust_mail_stop AS "Pos MD", ' '
FROM psoft.ps_position_data p
WHERE NOT EXISTS (
SELECT a.POSITION_NBR, a.EFFDT, a.deptid, b.cust_mail_stop
FROM psoft.ps_position_data a, psoft.ps_dept_tbl b
WHERE a.deptid(+) = b.deptid
AND b.effdt = (SELECT MAX(b2.effdt)
FROM psoft.ps_dept_tbl b2
WHERE b2.setid = b.setid
AND b2.deptid = b.deptid
AND b2.effdt <= a.effdt)
AND a.position_nbr = p.position_nbr)

I then added a union statement between Query 1 and Query 2. This combined query returned all 35,000 rows, thus accomplishing what I could not accomplish because outer joins don't work well with effective dated subqueries.

The combined query is below.

SELECT a.POSITION_NBR, a.EFFDT, a.deptid, a.mail_drop, a.qvc_mail_stop AS "Pos MP", b.cust_mail_stop
FROM psoft.ps_position_data a, psoft.ps_dept_tbl b
WHERE a.deptid(+) = b.deptid
AND b.effdt = (SELECT MAX(b2.effdt)
FROM psoft.ps_dept_tbl b2
WHERE b2.setid = b.setid
AND b2.deptid = b.deptid
AND b2.effdt <= a.effdt)
UNION
SELECT p.POSITION_NBR, p.EFFDT, p.deptid, p.mail_drop, p.cust_mail_stop AS "Pos MD", ' '
FROM psoft.ps_position_data p
WHERE NOT EXISTS (
SELECT a.POSITION_NBR, a.EFFDT, a.deptid, b.cust_mail_stop
FROM psoft.ps_position_data a, psoft.ps_dept_tbl b
WHERE a.deptid(+) = b.deptid
AND b.effdt = (SELECT MAX(b2.effdt)
FROM psoft.ps_dept_tbl b2
WHERE b2.setid = b.setid
AND b2.deptid = b.deptid
AND b2.effdt <= a.effdt)
AND a.position_nbr = p.position_nbr)