Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Syntax Issue - Need proper outer join to include records

Posted on 2011-10-19
21
Medium Priority
?
840 Views
Last Modified: 2012-05-12
I have added one column to a report. This required joining 2 tables not in the original query. The join I created is working well, except in the cases where there should be null values in the new column. In those cases, the records are not included in the query output. SQL query attachment shows where I added the additional tables and joins. The poh.segment1 column should be null in some cases. Where it is null, records do not show.
select pv.vendor_name
    ,pv.segment1 vendor_num
    ,poh.segment1 po_number
    ,ai.invoice_num 
   ,to_char(ai.invoice_date,'DD-MON-YYYY') invoice_date
    ,to_char(aid.creation_date,'DD-MON-YYYY') creation_date
    ,to_char(aid.accounting_date,'DD-MON-YYYY') acct_dt
    ,aid.distribution_line_number ln
    ,gcc.segment1       ||'.'||
        gcc.segment2    ||'.'||
        gcc.segment3    ||'.'||
        gcc.segment4    ||'.'||
        gcc.segment5    ||'.'||
        gcc.segment6            accounting_code
    ,aid.amount ln_amt
    ,gcc.segment1||' - '||fv1.description entity
    ,gcc.segment2||' - '||fv2.description account
    ,hou.name oper_unit
from ap_invoices_all ai
    ,ap_invoice_distributions_all aid
    ,gl_code_combinations gcc
    ,hr_operating_units hou
    ,fnd_flex_values_vl fv1
    ,fnd_flex_values_vl fv2
    ,fnd_id_flex_segments_vl ifs1
    ,fnd_id_flex_segments_vl ifs2
    ,po_vendors pv
    ,po_headers_all poh                ---Newly included table
    ,po_distributions_all pod          ---Newly included table 
    
where 1=1
and ai.invoice_id = aid.invoice_id
and aid.dist_code_combination_id = gcc.code_combination_id
and ai.org_id = hou.organization_id
and ai.vendor_id = pv.vendor_id
and poh.vendor_id = pv.vendor_id        ---Newly included join
and poh.po_header_id = pod.po_header_id ---Newly included join
AND pod.po_distribution_id = aid.po_distribution_id ---Newly included join


and fv1.flex_value_set_id = ifs1.flex_value_set_id
         AND fv1.flex_value = gcc.segment1
         AND ifs1.id_flex_code = 'GL#'
         AND ifs1.segment_name = 'Entity Code'
and fv2.flex_value_set_id = ifs2.flex_value_set_id
         AND fv2.flex_value = gcc.segment2
         AND ifs2.id_flex_code = 'GL#'
         AND ifs2.segment_name = 'Account Code'

Open in new window

0
Comment
Question by:janthonyn
  • 10
  • 6
  • 3
  • +1
21 Comments
 
LVL 24

Expert Comment

by:johanntagle
ID: 36996763
poh.segment is not in your join columns.  Something else, either poh.vendor_id or poh.po_header_id has null values.  You need to determine which.  Then see http://www.dba-oracle.com/tips_oracle_left_outer_join.htm for how to do a left outer join
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36996765
Use the old outer join syntax.

In the where on the side where you need to fill in(might be reversed I always mix it up), add a (+).

For example if you have:
And col1 = col2

Do either:
And col1(+) = col2
Or
And col1 = col2(+)
0
 

Author Comment

by:janthonyn
ID: 36997438
I know how to do an outer join and have tried several, but none worked. The joins are correct except for that. None of these resulted in outputting the records with poh.segment1 as null:

and poh.vendor_id = pv.vendor_id        
and poh.po_header_id = pod.po_header_id
AND pod.po_distribution_id(+) = aid.po_distribution_id

and poh.vendor_id = pv.vendor_id        
and poh.po_header_id = pod.po_header_id
AND pod.po_distribution_id = aid.po_distribution_id(+)

and poh.vendor_id = pv.vendor_id        
and poh.po_header_id = pod.po_header_id(+)
AND pod.po_distribution_id = aid.po_distribution_id

and poh.vendor_id = pv.vendor_id        
and poh.po_header_id(+) = pod.po_header_id
AND pod.po_distribution_id = aid.po_distribution_id

and poh.vendor_id = pv.vendor_id        
and poh.po_header_id(+) = pod.po_header_id
AND aid.po_distribution_id = pod.po_distribution_id(+)

and poh.vendor_id = pv.vendor_id        
and poh.po_header_id(+) = pod.po_header_id
AND aid.po_distribution_id(+) = pod.po_distribution_id

and poh.vendor_id(+) = pv.vendor_id        
and poh.po_header_id = pod.po_header_id
AND aid.po_distribution_id(+) = pod.po_distribution_id

and ai.invoice_id = aid.invoice_id(+)
and aid.dist_code_combination_id = gcc.code_combination_id
and ai.vendor_id = pv.vendor_id
and poh.vendor_id(+) = pv.vendor_id      
and poh.po_header_id = pod.po_header_id
AND aid.po_distribution_id = pod.po_distribution_id(+)

No answer has moved this forward so far.

0
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!

 
LVL 24

Expert Comment

by:johanntagle
ID: 36997459
Somewhere along the multiple joins and the filters the rows with null values for poh.segment1 are getting eliminated.  Without seeing your actual data I don't think there's a way for us to know.  Work from poh first - get the rows where segment1 is null then see what rows in the other tables they correspond to.  Start with a simple query first joining it with one table, then continue to add other tables and filters and see where it gets eliminated.
0
 

Author Comment

by:janthonyn
ID: 36997462
I have attached a new version of the code, greatly simplified, to see if anyone can detect what the outer join should be. The problem remains as follows. There are records that are not being pulled by this query that need to be included in the report. These records are the ones with a null value for the poh.segment1 column.
select pv.segment1 vendor_num
    ,poh.segment1 po_number
    ,ai.invoice_num 
    ,aid.distribution_line_number ln
    ,aid.amount ln_amt
from ap_invoices_all ai
    ,ap_invoice_distributions_all aid
    ,po_vendors pv
    ,po_headers_all poh                ---Newly included table
    ,po_distributions_all pod          ---Newly included table 
where 1=1
and ai.invoice_id = aid.invoice_id
and ai.vendor_id = pv.vendor_id
and poh.vendor_id = pv.vendor_id       
and poh.po_header_id = pod.po_header_id 
and aid.po_distribution_id = pod.po_distribution_id

Open in new window

0
 

Author Comment

by:janthonyn
ID: 36997468
BTW, if I remove the Newly included tables and the associated joins, all records are returned. But in this case, I have removed the column that I must add to complete the report, the pom.segment1 column. Does anyone have any questions about this problem? Do you get what I'm trying to do?
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 36997514
How's the relationship between:

1. po_headers_all and po_distributions_all
2. po_headers_all and po_vendors
3. ap_invoice_distributions_all and po_distributions_all

Which ones of the above can possibly have non-matching rows based on the join columns?
0
 
LVL 31

Expert Comment

by:hnasr
ID: 36999100
Troubleshoot the query. Include 2 columns of the working query, and add a column from the newly joined tables. You can add more columns and conditions. Comment here.

If problem not resolved, list 3 records of the old (working) query and 3 records from the added tables and the expected result. Limit the output to 3 columns.
0
 

Author Comment

by:janthonyn
ID: 36999553
I have run the query with the PO tables added new in it for 3 columns and another without the newly added tables. I limited the query to one invoice. The invoice has 28 distribution records, all of which output only in the second query. Output is attached for both.
With-PO-table-joins.xls
0
 

Author Comment

by:janthonyn
ID: 36999558
The second attachment here.
Without-PO-table-joins.xls
0
 
LVL 31

Expert Comment

by:hnasr
ID: 36999798
I can see that with the join 2 records with LN 27 and 28 are dropped.
Where is the fields from the other joined table?
I cannot reproduce the issue.
To help troubleshoot the problem I list here 4 records of the query.
INVOICE_NO LN       LN_AMT
70351575         24      38.82
70351575         25      4.68
70351575   26      32.49
70351575         27      5.49
70351575         28      61.65

Can you add the few records in the other table to be joined?
0
 

Author Comment

by:janthonyn
ID: 37000268
poh.segment1 has null values in 27, 28 and is not output by the updated query.
0
 
LVL 31

Expert Comment

by:hnasr
ID: 37001037
Help me to recreate the problem.
I'll assume having one table (the no join query), with the records displayed above.
Supply me with the one other table to be joined.
otherTable: (col1, col2, col3, col4) with few records.
0
 

Accepted Solution

by:
janthonyn earned 0 total points
ID: 37001704
I figured it out. Code attached.
SELECT   pv.segment1 vendor_num,
           poh.segment1 po_number,
           ai.invoice_num,
           aid.distribution_line_number LN,
           aid.amount ln_amt
    FROM   ap_invoices_all ai,
           ap_invoice_distributions_all aid,
           po_vendors pv,
           po_headers_all poh,
           po_distributions_all pod
   WHERE       1 = 1
           AND ai.invoice_id = aid.invoice_id
           AND ai.vendor_id = pv.vendor_id
           AND poh.po_header_id (+)= pod.po_header_id
           AND pod.po_distribution_id(+) = aid.po_distribution_id

Open in new window

0
 

Author Closing Comment

by:janthonyn
ID: 37023081
I was able to figure out the outer joins needed to output all records sought.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37001736
So, the (+) ended up working that was absent from your original post?  Seems like that at least assisted in the solution.
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 37003602
janthonyn, I don't mind not getting points, but for people to be able to better assist you in the future I think you should understand that this is one type of question that offered no way for us to give you the exact answer without seeing your data.  That's why I and later on hnasr advised you to start with a much simpler query then add joins and filter to determine which exact code is problematic.  Seeing that you still didn't provide that data after my #36997459 post I then asked you for the relationships between certain tables, asking which ones could possibly have non-matching rows when you join them.  You didn't answer that too.  Then later hnasr asked for sample data to replicate the issue, which you also didn't provide.  I guess you were really hesitant to provide the data, but if such was the case, it would be best to just say so, so we all know for sure what information we have to work with.  If the best we can provide given the information you provided is guidance, then so be it.
0
 

Author Comment

by:janthonyn
ID: 37004030
I did provide the output and I did provide a simpler code. You simply ignored my posts.
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 37004267
We weren't asking for the query output, we were asking for the actual data in the tables.
0
 

Author Comment

by:janthonyn
ID: 37004360
The problem with e-e is nobody here knows Oracle database. I've found help in other areas, but not Oracle questions. Plus problem solved and didn't get any helpnhere. But since your whining so much I'll end this by assigning points
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 37004368
As I said, I don't mind not getting any points.  Wow - NOBODY knows Oracle database?  The problem is you don't know how to post your question correctly and provide the information needed to have your problem solved.

I don't want your points.  I'll also take care to ignore any of your questions from now on.  Have a nice life.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

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 month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

571 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