How to convert old style Sql Outer join with condition to the new style

Question

I need to implement the old style SQL outer join with condition to the new style.

I am unable to convert the last line of the Join with condition.

Old Style Query:

SELECT cpd.customer_ref, cpd.product_seq, bci.contract_inst_id, 
bci.start_dat  bci_start_dat, bci.end_dat bci_end_dat 
FROM custproductdetails cpd, balcontractinstance bci
WHERE cpd.customer_ref = bci.customer_ref(+)
AND cpd.contract_seq = bci.contract_seq(+)
AND cpd.end_dat >=  bci.start_dat(+) AND cpd.end_dat <= bci.end_dat(+);

There are many more answers to this: https://stackoverflow.com/search?page=2&tab=Relevance&q=%5boracle%5d%20convert%20outer%20join — , Oct 31 '17 at 11:27

score 0 · Answer 1 · answered Oct 31 '17 at 10:57

0

I assume you are going from the oracle syntax to sqlserver etc, which requires the ansi syntax, which is better to use going forward in general, regardless of database

Select a.a,b.a From a left join b on a.a = b.a

Is the same as

Select a.a, b.a From a, b Where a.a = b.a(+)

answered Oct 31 '17 at 10:57

Ab Bennett

1,391
17
24

I could convert so far as shown below. Need idea on the last condition. – Sunil Ch Oct 31 '17 at 10:59
SELECT cpd.customer_ref, cpd.product_seq, bci.contract_inst_id, bci.start_dat bci_start_dat, bci.end_dat bci_end_dat FROM custproductdetails cpd LEFT OUTER JOIN balcontractinstance bci ON cpd.customer_ref = bci.customer_ref AND cpd.contract_seq = bci.contract_seq – Sunil Ch Oct 31 '17 at 10:59
As I am planning to execute the above query in HIVE – Sunil Ch Oct 31 '17 at 11:01
What you have looks fine – Ab Bennett Oct 31 '17 at 11:03

score 0 · Answer 2 · answered Oct 31 '17 at 10:58

I believe this does what you want:

SELECT cpd.customer_ref, cpd.product_seq, bci.contract_inst_id, 
       bci.start_dat  bci_start_dat, bci.end_dat bci_end_dat 
FROM custproductdetails cpd LEFT JOIN
     balcontractinstance bci
     ON cpd.customer_ref = bci.customer_ref AND
        cpd.contract_seq = bci.contract_seq AND
        cpd.end_dat >=  bci.start_dat AND
        cpd.end_dat <= bci.end_dat;

The general rule is that the table referenced with the (+) gets additional rows added. That makes it the second table in the LEFT JOIN.

Thanks for the answer. It did work for me. – Sunil Ch Oct 31 '17 at 11:29 — Sunil Ch, Oct 31 '17 at 11:29

score 0 · Answer 3 · answered Oct 31 '17 at 11:10

Old Style Query:

SELECT cpd.customer_ref, cpd.product_seq, bci.contract_inst_id, 
    bci.start_dat  bci_start_dat, bci.end_dat bci_end_dat 
    FROM custproductdetails cpd, balcontractinstance bci
    WHERE cpd.customer_ref = bci.customer_ref(+)
    AND cpd.contract_seq = bci.contract_seq(+)
    AND cpd.end_dat >= bci.start_dat(+) 
    AND cpd.end_dat <= bci.end_dat(+);

'New' Style Query:

SELECT cpd.customer_ref, cpd.product_seq, bci.contract_inst_id, 
    bci.start_dat  bci_start_dat, bci.end_dat bci_end_dat 
    FROM custproductdetails cpd LEFT JOIN balcontractinstance bci
    ON cpd.customer_ref = bci.customer_ref
    AND cpd.contract_seq = bci.contract_seq
    AND cpd.end_dat >= bci.start_dat 
    AND cpd.end_dat <= bci.end_dat;

As you can see it's pretty similar.

How to convert old style Sql Outer join with condition to the new style

3 Answers3