asked on

SQL Syntax Issue - Need proper outer join to include records

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

johanntagle

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

slightwv (䄆 Netminder)

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(+)

janthonyn

ASKER

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.

johanntagle

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.

janthonyn

ASKER

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

janthonyn

ASKER

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?

johanntagle

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?

Hamed Nasr

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.

janthonyn

ASKER

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

janthonyn

ASKER

The second attachment here.
Without-PO-table-joins.xls

Hamed Nasr

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?

janthonyn

ASKER

poh.segment1 has null values in 27, 28 and is not output by the updated query.

Hamed Nasr

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.

ASKER CERTIFIED SOLUTION

janthonyn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

janthonyn

ASKER

I was able to figure out the outer joins needed to output all records sought.

slightwv (䄆 Netminder)

So, the (+) ended up working that was absent from your original post? Seems like that at least assisted in the solution.

johanntagle

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.

janthonyn

ASKER

I did provide the output and I did provide a simpler code. You simply ignored my posts.

johanntagle

We weren't asking for the query output, we were asking for the actual data in the tables.

janthonyn

ASKER

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

johanntagle

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.