Solved

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

Posted on 2014-01-17
9
393 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
  • 5
  • 4
9 Comments
 
LVL 73

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 73

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 73

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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

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 73

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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 information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

757 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now