Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Oracle 9i - Add a left outer join

Posted on 2014-11-11
16
336 Views
Last Modified: 2015-04-29
Experts,
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

Open in new window


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
0
Comment
Question by:JDCam
  • 6
  • 5
  • 3
  • +2
16 Comments
 
LVL 39

Expert Comment

by:lcohan
ID: 40435945
You could try change:

AND L.LOC_CODE  = D5D1.LOC_CODE


to:

AND (L.LOC_CODE  = D5D1.LOC_CODE OR D5D1.LOC_CODE IS NULL)
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40435991
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
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 40436009
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_code)
           AND l.whse_code = nvl(d5d1.whse_co,l.whse_code)
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 74

Expert Comment

by:sdstuber
ID: 40436028
>> Oracle9 supports the ANSI-standard "left outer join" syntax

9i is when the ANSI support was introduced.
0
 

Author Comment

by:JDCam
ID: 40436045
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
0
 

Author Comment

by:JDCam
ID: 40436049
sorry.. that was for Lochan.
Trying Sdstuber solution now
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 40436052
"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]
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 40436057
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.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40436058
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
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40436061
>>  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.
0
 

Author Comment

by:JDCam
ID: 40436072
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
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 250 total points
ID: 40436075
each join can produce a cartesian expansion.

You had some commented out conditions in your original query,  I removed them in my post.  Should they be resored?

And, you may need to apply a DISTINCT
0
 

Author Comment

by:JDCam
ID: 40437583
no luck....  Its also become very slow to run.
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)))),'*')

Open in new window

--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)))),'*')

Open in new window

0
 

Author Comment

by:JDCam
ID: 40437632
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.
0
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 250 total points
ID: 40437727
I admit that I am not an expert on ANSI-standard left join syntax.  But if there really is no record in the left-joined (or outer-joined) table (D5D1 in this case) then based on what I understand, conditions like this will never be true:

         AND l.loc_code = d5d1.loc_code
         AND l.whse_code = d5d1.whse_code
0
 

Author Closing Comment

by:JDCam
ID: 40750664
Sorry.. never completely resolved. Will split the points
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Database Design Dilemma 6 58
Procedure syntax 5 38
UTL_FILE invalid file operation 5 25
Requesting help with creating an SQL query to select similar records 4 14
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question