Solved

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

Posted on 2014-01-17
9
397 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
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 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 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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle - Create Procedure with Paramater 16 64
Web Service from a stored procdure oracle 10 55
ORA-00923: FROM keyword not found where expected 3 64
run sql script from putty 4 36
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…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

777 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