Solved

Joining tables with Outer Join to display all rows, even if data is null

Posted on 2014-01-17
9
401 Views
Last Modified: 2014-01-17
Here is my present sequel statement, but results are now back to only showing rows that have a DEL_DOC_NUM.

SELECT PO.PO_CD, PO.VE_CD, PO_LN.QTY_ORD, PO_LN.ITM_CD, ITM.DES, PO_LN.DEST_STORE_CD, SE_PART2PO_LN.DEL_DOC_NUM, CUST.LNAME
FROM INV.ITM ITM, MERCH.PO PO, MERCH.PO_LN PO_LN, SERVICE.SE_PART2PO_LN SE_PART2PO_LN, CUST, SO
WHERE PO_LN.PO_CD = PO.PO_CD AND PO_LN.ITM_CD = ITM.ITM_CD AND PO.PO_CD = SE_PART2PO_LN.PO_CD (+) AND  SO.DEL_DOC_NUM = SERVICE.SE_PART2PO_LN.DEL_DOC_NUM AND SO.CUST_CD = CUST.CUST_CD AND ((PO.STAT_CD='O') AND (PO.STORE_CD In ('05','06','07','09')))



PO CD                  VEND      Q      SKU            DEL_DOC_NUM   LNAME
1211306TDRN          AFM      1      V1386      1211306TDRN         HALLENBECK
1211306TDRNA   AFM      1      V7122      1211306TDRN         HALLENBECK
0109407TIOA          ASHL      1      V1261      0109407TIOA         GOODVICH
0103409THNI          BASS      1      V9528      0103409THNI         KNIAT
1231309THAM          BEST      1      V9430      1231309THAM         MOORE


Should include:

PO CD                  VEND      Q      SKU            DEL_DOC_NUM   LNAME
58570-040812          AAME      1      V7800      
58570-04082          AAME      1      V6108      
58570-0713          AAME      1      V1840
0
Comment
Question by:drozeveld
[X]
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
  • 5
  • 4
9 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 39789738
when posting sample data, please post it for EVERY table in the query, not the results of the query.

In this case though, I think I might be able to figure it out with out the data.

If you use the oracle join syntax you must put the (+)  on every condition that defines the outer join.

by putting only one the join is effectively an inner join



SELECT po.po_cd,
       po.ve_cd,
       po_ln.qty_ord,
       po_ln.itm_cd,
       itm.des,
       po_ln.dest_store_cd,
       se_part2po_ln.del_doc_num,
       cust.lname
  FROM inv.itm,
       merch.po,
       merch.po_ln,
       service.se_part2po_ln,
       cust,
       so
 WHERE po_ln.po_cd = po.po_cd
   AND po_ln.itm_cd = itm.itm_cd
   AND po.po_cd = se_part2po_ln.po_cd(+)
   AND so.del_doc_num = se_part2po_ln.del_doc_num(+)
   AND so.cust_cd = cust.cust_cd
   AND po.stat_cd = 'O'
   AND po.store_cd IN ('05',
                       '06',
                       '07',
                       '09')
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39789741
actually, no,that won't work.  This requires ansi join syntax because you must outer join to more than one table
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 39789747
try this

SELECT po.po_cd,
       po.ve_cd,
       po_ln.qty_ord,
       po_ln.itm_cd,
       itm.des,
       po_ln.dest_store_cd,
       se_part2po_ln.del_doc_num,
       cust.lname
  FROM merch.po
       INNER JOIN merch.po_ln ON po_ln.po_cd = po.po_cd
       INNER JOIN inv.itm ON itm.itm_cd = po_ln.itm_cd
       LEFT OUTER JOIN service.se_part2po_ln ON se_part2po_ln.po_cd = po.po_cd
       LEFT OUTER JOIN so ON so.del_doc_num = se_part2po_ln.del_doc_num
       LEFT OUTER JOIN cust ON cust.cust_cd = so.cust_cd
 WHERE po.stat_cd = 'O'
   AND po.store_cd IN ('05',
                       '06',
                       '07',
                       '09')
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 1

Author Comment

by:drozeveld
ID: 39789751
I'm not sure I understand what "data" you are requesting, but you are correct, it didn't work because I tried adding the (+) to all the tables and it went back to excluding the PO's that don't have a DEL_DOC_NUM.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39789752
you could try the oracle outer join syntax like this


SELECT po.po_cd,
       po.ve_cd,
       po_ln.qty_ord,
       po_ln.itm_cd,
       itm.des,
       po_ln.dest_store_cd,
       se_part2po_ln.del_doc_num,
       cust.lname
  FROM inv.itm,
       merch.po,
       merch.po_ln,
       service.se_part2po_ln,
       cust,
       so
 WHERE po_ln.po_cd = po.po_cd
   AND po_ln.itm_cd = itm.itm_cd
   AND po.po_cd = se_part2po_ln.po_cd(+)
   AND so.del_doc_num(+) = se_part2po_ln.del_doc_num
   AND so.cust_cd = cust.cust_cd(+)
   AND po.stat_cd = 'O'
   AND po.store_cd IN ('05',
                       '06',
                       '07',
                       '09')


If these don't work, please provide a complete set of data to build test cases from
as well as expected results

I don't need the current results, if I have sample data, I can generate that myself.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39789760
By sample data I mean a few rows for each of of  the tables...

itm
po
po_ln
se_part2po_ln,
cust
so

I don't have your database or your data, so I can't test anything I post.
If you give me sample data to work with it eliminates all guess work. I write the query, test it, when it gives the results you ask for I post it.
0
 
LVL 1

Author Comment

by:drozeveld
ID: 39789762
It worked!!

Thanks again.  You've been VERY helpful!!
0
 
LVL 1

Author Closing Comment

by:drozeveld
ID: 39789763
Quick responses and very easy to understand instructions.
0
 
LVL 1

Author Comment

by:drozeveld
ID: 39789767
OK, I understand what you mean now.   Have a great weekend!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to drop system generated virtual column in a table in12c 15 196
Procedure syntax 5 65
Updating a temp table inside a PL/SQL block 3 65
SQL Syntax Question 9 57
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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 syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

752 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