Skip to Content
0
Former Member
Jul 14, 2009 at 08:05 PM

Left Outer Join Not Working as Expected

769 Views

I have a problem that I can't seem to get my head around. I have two tables from our DB2 ERP system that I need to preform a left outer join on. The left table 1 is our Item Master table and table 2 is our production schedule table. I want to show all parts from table 1 and there corosponding records from table 2 including nulls. The problem I seem to have is I need to use the selection expert to filter my data down to only show records from table 2 that are from a particlar production schedule which is filter using the production schedule ID#. When I do this I only get records that have a value in table 2. If dont use is selection criteria on table 2 and just do a left outer join it seems to work, but I have way more data than I want since I need to filter it down base on the schedule ID # from table 2. Here is an my SQL with no selection criteria from table 2.

SELECT "DBHSREP"."HSDFC3", "DBHSREP"."HSR0FQ", "DBHSREP"."HSJFDT", "DBCZREP"."CZEKCD", "DBCZREP"."CZAZCD"

FROM {oj "MACPAC"."VNGDBDTA"."DBCZREP" "DBCZREP" LEFT OUTER JOIN "MACPAC"."VNGDBDTA"."DBHSREP" "DBHSREP" ON ("DBCZREP"."CZAZCD"="DBHSREP"."HSAZCD") AND ("DBCZREP"."CZEKCD"="DBHSREP"."HSEKCD")}

WHERE "DBCZREP"."CZAZCD"='MTS'

Here is my SQL if I try to use selection criteria from table 2

SELECT "DBHSREP"."HSDFC3", "DBHSREP"."HSR0FQ", "DBHSREP"."HSJFDT", "DBCZREP"."CZEKCD", "DBCZREP"."CZAZCD"

FROM {oj "MACPAC"."VNGDBDTA"."DBCZREP" "DBCZREP" LEFT OUTER JOIN "MACPAC"."VNGDBDTA"."DBHSREP" "DBHSREP" ON ("DBCZREP"."CZAZCD"="DBHSREP"."HSAZCD") AND ("DBCZREP"."CZEKCD"="DBHSREP"."HSEKCD")}

WHERE "DBCZREP"."CZAZCD"='MTS' AND "DBHSREP"."HSDFC3"='000299'

Is there any easy way to fix this to allow for my selection criteria. Thanks in Advance