Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-01-17
9
Medium Priority
?
405 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 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 2000 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
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…
Suggested Courses

824 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