JDCam
asked on
Oracle 9i - Add a left outer join
Experts,
I am having problems with a table join in a query. Here is the join section
for some records there is no value in D5D1.LOC_CODE.
beacuse of L.LOC_CODE = D5D1.LOC_CODE , the entire row is omitted. I need to keep the row but leave the column blank.
I think I need an outer left join, but I am way over my head here..... can anyone help please
I am having problems with a table join in a query. Here is the join section
FROM E_ORD_D5D1 D5D1, E_Ord_D7 D7, E_ORD_D5 D5, M_LOC L
WHERE D5.COMP_CODE = :p_comp_code
AND D5.ORD_NUM = :p_ord_num
AND D7.Comp_Code = D5.Comp_Code
AND D7.Ord_Num = D5.Ord_Num
AND D5D1.Comp_Code (+) = D5.Comp_Code
AND D5D1.Ord_Num (+) = D5.Ord_Num
AND D5D1.Ord_Line_Num (+) = D5.Ord_Line_Num
AND D5D1.Ord_Loc_Line_Num (+) > 0
--AND D5D1.Ord_Loc_Qty > 0
--AND D5D1.Whse_Code <> '*'
--AND D5D1.Loc_Code <> '*'
AND L.COMP_CODE = D5.COMP_CODE
AND L.LOC_CODE = D5D1.LOC_CODE
AND L.WHSE_CODE = D5D1.WHSE_CODE
for some records there is no value in D5D1.LOC_CODE.
beacuse of L.LOC_CODE = D5D1.LOC_CODE , the entire row is omitted. I need to keep the row but leave the column blank.
I think I need an outer left join, but I am way over my head here..... can anyone help please
FROM e_ord_d5 d5
INNER JOIN e_ord_d7 d7 ON d7.comp_code = d5.comp_code AND d7.ord_num = d5.ord_num
INNER JOIN m_loc l ON l.comp_code = d5.comp_code
LEFT OUTER JOIN e_ord_d5d1 d5d1
ON d5d1.comp_code = d5.comp_code
AND d5d1.ord_num = d5.ord_num
AND d5d1.ord_line_num = d5.ord_line_num
AND d5d1.ord_loc_line_num > 0
AND l.loc_code = d5d1.loc_code
AND l.whse_code = d5d1.whse_co
WHERE d5.comp_code = :p_comp_code AND d5.ord_num = :p_ord_num
INNER JOIN e_ord_d7 d7 ON d7.comp_code = d5.comp_code AND d7.ord_num = d5.ord_num
INNER JOIN m_loc l ON l.comp_code = d5.comp_code
LEFT OUTER JOIN e_ord_d5d1 d5d1
ON d5d1.comp_code = d5.comp_code
AND d5d1.ord_num = d5.ord_num
AND d5d1.ord_line_num = d5.ord_line_num
AND d5d1.ord_loc_line_num > 0
AND l.loc_code = d5d1.loc_code
AND l.whse_code = d5d1.whse_co
WHERE d5.comp_code = :p_comp_code AND d5.ord_num = :p_ord_num
I'm not sure that Oracle9 supports the ANSI-standard "left outer join" syntax. That may require the Oracle-specific outer-join syntax like you have in your question (with the "(+)" operator next to the column value that may be null).
I'm sure the problem is these two lines:
AND l.loc_code = d5d1.loc_code
AND l.whse_code = d5d1.whse_co
If that table needs to be "outer joined" (because there are no matching records there for some records in the d5 table) then you can't have standard join syntax here between that table and the m_loc table. Try changing those two lines to this:
AND l.loc_code = nvl(d5d1.loc_code,l.loc_co de)
AND l.whse_code = nvl(d5d1.whse_co,l.whse_co de)
I'm sure the problem is these two lines:
AND l.loc_code = d5d1.loc_code
AND l.whse_code = d5d1.whse_co
If that table needs to be "outer joined" (because there are no matching records there for some records in the d5 table) then you can't have standard join syntax here between that table and the m_loc table. Try changing those two lines to this:
AND l.loc_code = nvl(d5d1.loc_code,l.loc_co
AND l.whse_code = nvl(d5d1.whse_co,l.whse_co
>> Oracle9 supports the ANSI-standard "left outer join" syntax
9i is when the ANSI support was introduced.
9i is when the ANSI support was introduced.
ASKER
No error, but the row is still missing.
I determined that it is not just D5D1.LOC_CODE that is null, but there is no matching record in table D5D1
I determined that it is not just D5D1.LOC_CODE that is null, but there is no matching record in table D5D1
ASKER
sorry.. that was for Lochan.
Trying Sdstuber solution now
Trying Sdstuber solution now
"there is no matching record in table D5D1"
Yes, that explains why you need to use either Oracle's outer join operator (+), or the ANSI-standard left outer join syntax. And, in either case you cannot then include a simple comparison like this:
AND [something] = d5d1.[anything]
Yes, that explains why you need to use either Oracle's outer join operator (+), or the ANSI-standard left outer join syntax. And, in either case you cannot then include a simple comparison like this:
AND [something] = d5d1.[anything]
By all means use ANSI outer joins! As Sean told, it came with 9i (= 9.2). Prior releases including 9.0 require the proprietary and error-prone (+) syntax, which should be avoided because of its irregular results with complex join conditions.
The old style (+) won't work here because you can't use that syntax to outer join a table to more than one other table
Hence the conversion to ANSI join syntax
Hence the conversion to ANSI join syntax
>> error-prone (+) syntax,
actually, in 9i, the ANSI support was a little iffy since it was new. The (+) support was still better then.
But, as I noted above, it's not really an option here since the one table needs to be double-outer joined and (+) simply doesn't support it.
actually, in 9i, the ANSI support was a little iffy since it was new. The (+) support was still better then.
But, as I noted above, it's not really an option here since the one table needs to be double-outer joined and (+) simply doesn't support it.
ASKER
I tried stdtubers solution
I now get 110 rows instead of the 3 expected.
it looks like each result is repeating for every possible value in the L table
I now get 110 rows instead of the 3 expected.
it looks like each result is repeating for every possible value in the L table
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
no luck.... Its also become very slow to run.
Here is the entire query. Maybe something jumps out?
Here is the entire query. Maybe something jumps out?
--NEW ATTEMPT
SELECT D5.COMP_CODE,
D5.ORD_NUM,
D5.CUST_CODE,
decode(D5D1.ORD_LOC_QTY,0,'',D5D1.Loc_Code) LOC_CODE,
NVL(decode(ORD_INVT_LEV1,'INVT_LEV1',ORD_LEV1,
DECODE(ORD_INVT_LEV2,'INVT_LEV1',ORD_LEV2,
DECODE(ORD_INVT_LEV3,'INVT_LEV1',ORD_LEV3,
DECODE(ORD_INVT_LEV4,'INVT_LEV1',ORD_LEV4)))),'*') ITEM_CODE,
k4crystal_rep.f4any_col('ITEM_DES1','M_ITEM_H','C',"D5"."COMP_CODE",
'CUST_CODE',"D5"."CUST_CODE",'ITEM_CODE',
NVL(DECODE(ORD_INVT_LEV1,'INVT_LEV1',ORD_LEV1,
DECODE(ORD_INVT_LEV2,'INVT_LEV1',ORD_LEV2,
DECODE(ORD_INVT_LEV3,'INVT_LEV1',ORD_LEV3,
DECODE(ORD_INVT_LEV4,'INVT_LEV1',ORD_LEV4)))),'*')) ITEM_DES1,
sum(NVL(D5.ORD_ORD_QTY, 0)) ORD_QTY,
sum(nvl(D5D1.ORD_LOC_QTY, 0)) SHIP_QTY,
sum(decode(ORD_TOT_WGT,0,0,NVL(f4conv_wgt_meas(D5.Wgt_Meas_Code,ORD_TOT_WGT,'LBS'), 0) / NVL(ORD_SHIP_QTY, 1))) * sum( NVL(D5D1.ORD_LOC_QTY,0)) GROSS_WGT,
L.ISOL_CODE PICK_ZONE
FROM e_ord_d5 d5
INNER JOIN e_ord_d7 d7 ON d7.comp_code = d5.comp_code AND d7.ord_num = d5.ord_num
INNER JOIN m_loc l ON l.comp_code = d5.comp_code
LEFT OUTER JOIN e_ord_d5d1 d5d1
ON d5d1.comp_code = d5.comp_code
AND d5d1.ord_num = d5.ord_num
AND d5d1.ord_line_num = d5.ord_line_num
AND d5d1.ord_loc_line_num > 0
AND l.loc_code = d5d1.loc_code
AND l.whse_code = d5d1.whse_code
WHERE d5.comp_code = :p_comp_code AND d5.ord_num = :p_ord_num
GROUP BY D5.COMP_CODE, D5.ORD_NUM, D5.CUST_CODE, decode(D5D1.ORD_LOC_QTY,0,'',D5D1.Loc_Code), NVL(decode(ORD_INVT_LEV1,'INVT_LEV1',ORD_LEV1,
DECODE(ORD_INVT_LEV2,'INVT_LEV1',ORD_LEV2,
DECODE(ORD_INVT_LEV3,'INVT_LEV1',ORD_LEV3,
DECODE(ORD_INVT_LEV4,'INVT_LEV1',ORD_LEV4)))),'*'), k4crystal_rep.f4any_col('ITEM_DES1','M_ITEM_H','C',"D5"."COMP_CODE",
'CUST_CODE',"D5"."CUST_CODE",'ITEM_CODE',
NVL(DECODE(ORD_INVT_LEV1,'INVT_LEV1',ORD_LEV1,
DECODE(ORD_INVT_LEV2,'INVT_LEV1',ORD_LEV2,
DECODE(ORD_INVT_LEV3,'INVT_LEV1',ORD_LEV3,
DECODE(ORD_INVT_LEV4,'INVT_LEV1',ORD_LEV4)))),'*')), L.ISOL_CODE
ORDER BY
decode(D5D1.ORD_LOC_QTY,0,'',D5D1.Loc_Code),
NVL(decode(ORD_INVT_LEV1,'INVT_LEV1',ORD_LEV1,
DECODE(ORD_INVT_LEV2,'INVT_LEV1',ORD_LEV2,
DECODE(ORD_INVT_LEV3,'INVT_LEV1',ORD_LEV3,
DECODE(ORD_INVT_LEV4,'INVT_LEV1',ORD_LEV4)))),'*')
--NEW ATTEMPT
SELECT D5.COMP_CODE,
D5.ORD_NUM,
D5.CUST_CODE,
decode(D5D1.ORD_LOC_QTY,0,'',D5D1.Loc_Code) LOC_CODE,
NVL(decode(ORD_INVT_LEV1,'INVT_LEV1',ORD_LEV1,
DECODE(ORD_INVT_LEV2,'INVT_LEV1',ORD_LEV2,
DECODE(ORD_INVT_LEV3,'INVT_LEV1',ORD_LEV3,
DECODE(ORD_INVT_LEV4,'INVT_LEV1',ORD_LEV4)))),'*') ITEM_CODE,
k4crystal_rep.f4any_col('ITEM_DES1','M_ITEM_H','C',"D5"."COMP_CODE",
'CUST_CODE',"D5"."CUST_CODE",'ITEM_CODE',
NVL(DECODE(ORD_INVT_LEV1,'INVT_LEV1',ORD_LEV1,
DECODE(ORD_INVT_LEV2,'INVT_LEV1',ORD_LEV2,
DECODE(ORD_INVT_LEV3,'INVT_LEV1',ORD_LEV3,
DECODE(ORD_INVT_LEV4,'INVT_LEV1',ORD_LEV4)))),'*')) ITEM_DES1,
sum(NVL(D5.ORD_ORD_QTY, 0)) ORD_QTY,
sum(nvl(D5D1.ORD_LOC_QTY, 0)) SHIP_QTY,
sum(decode(ORD_TOT_WGT,0,0,NVL(f4conv_wgt_meas(D5.Wgt_Meas_Code,ORD_TOT_WGT,'LBS'), 0) / NVL(ORD_SHIP_QTY, 1))) * sum( NVL(D5D1.ORD_LOC_QTY,0)) GROSS_WGT,
L.ISOL_CODE PICK_ZONE
FROM e_ord_d5 d5
INNER JOIN e_ord_d7 d7 ON d7.comp_code = d5.comp_code AND d7.ord_num = d5.ord_num
INNER JOIN m_loc l ON l.comp_code = d5.comp_code
LEFT OUTER JOIN e_ord_d5d1 d5d1
ON d5d1.comp_code = d5.comp_code
AND d5d1.ord_num = d5.ord_num
AND d5d1.ord_line_num = d5.ord_line_num
AND d5d1.ord_loc_line_num > 0
AND l.loc_code = d5d1.loc_code
AND l.whse_code = d5d1.whse_code
WHERE d5.comp_code = :p_comp_code AND d5.ord_num = :p_ord_num
GROUP BY D5.COMP_CODE, D5.ORD_NUM, D5.CUST_CODE, decode(D5D1.ORD_LOC_QTY,0,'',D5D1.Loc_Code), NVL(decode(ORD_INVT_LEV1,'INVT_LEV1',ORD_LEV1,
DECODE(ORD_INVT_LEV2,'INVT_LEV1',ORD_LEV2,
DECODE(ORD_INVT_LEV3,'INVT_LEV1',ORD_LEV3,
DECODE(ORD_INVT_LEV4,'INVT_LEV1',ORD_LEV4)))),'*'), k4crystal_rep.f4any_col('ITEM_DES1','M_ITEM_H','C',"D5"."COMP_CODE",
'CUST_CODE',"D5"."CUST_CODE",'ITEM_CODE',
NVL(DECODE(ORD_INVT_LEV1,'INVT_LEV1',ORD_LEV1,
DECODE(ORD_INVT_LEV2,'INVT_LEV1',ORD_LEV2,
DECODE(ORD_INVT_LEV3,'INVT_LEV1',ORD_LEV3,
DECODE(ORD_INVT_LEV4,'INVT_LEV1',ORD_LEV4)))),'*')), L.ISOL_CODE
ORDER BY
decode(D5D1.ORD_LOC_QTY,0,'',D5D1.Loc_Code),
NVL(decode(ORD_INVT_LEV1,'INVT_LEV1',ORD_LEV1,
DECODE(ORD_INVT_LEV2,'INVT_LEV1',ORD_LEV2,
DECODE(ORD_INVT_LEV3,'INVT_LEV1',ORD_LEV3,
DECODE(ORD_INVT_LEV4,'INVT_LEV1',ORD_LEV4)))),'*')
ASKER
A thought..... the need for column L.ISOL_CODE to be returned is whats driving the need for table L to be joined in.
This column has only 6 possible values (Null being one of them), based on the value in D5D1.LOC_CODE.
What if we didn't add L table at all, and instead used a CASE or DECODE to set this column based on the value in D5D1.LOC_CODE.
WHEN D5D1.LOC_CODE LIKE '70%' THEN A
WHEN D5D1.LOC_CODE LIKE '80%' THEN B etc.....
What might this concept look like in the above query?
Remember that D5D1.LOC code can often be null, but should still return the row with a null column.
This column has only 6 possible values (Null being one of them), based on the value in D5D1.LOC_CODE.
What if we didn't add L table at all, and instead used a CASE or DECODE to set this column based on the value in D5D1.LOC_CODE.
WHEN D5D1.LOC_CODE LIKE '70%' THEN A
WHEN D5D1.LOC_CODE LIKE '80%' THEN B etc.....
What might this concept look like in the above query?
Remember that D5D1.LOC code can often be null, but should still return the row with a null column.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry.. never completely resolved. Will split the points
AND L.LOC_CODE = D5D1.LOC_CODE
to:
AND (L.LOC_CODE = D5D1.LOC_CODE OR D5D1.LOC_CODE IS NULL)