Conditional joining in oracle sql with if else or not

Question

How to do this in oracle sql i tried with different methods. Here i provided with if else query to understand what i need to perform

if(SELECT SLS_CONT_NO FROM GL WHERE GL.INVNO = '67883' AND GL.CUST_ID = '14714') is not null then SELECT DISTINCT M.NAME FROM GL JOIN SALES_CONT SLS ON GL.SLS_CONT_NO = SLS.SLS_CONT_NO JOIN MARKETER M ON M.MKTRINIT = SLS.MKTRINIT WHERE GL.INVNO = '67883' AND GL.CUST_ID = '14714'; ELSE SELECT DISTINCT M.NAME FROM GL JOIN TANK TA ON GL.DEST_TANK=TA.TANK_NO JOIN MARKETER M ON M.MKTRINIT = TA.MKTRINIT WHERE GL.INVNO = '67883' AND GL.CUST_ID = '14714'; end if

do you try union or left join (stackoverflow.com/questions/25074047/…) or nvl (stackoverflow.com/questions/36847983/…) ? — M. Rezaeyan
– M. Rezaeyan, Commented Aug 13, 2017 at 20:08

Ferdinand Gaspar · Accepted Answer · 2017-08-13 20:04:52Z

You can use CASE WHEN Statement

SELECT CASE WHEN a.SLS_CONT_NO IS NOT NULL THEN (SELECT DISTINCT M.NAME FROM GL b JOIN SALES_CONT SLS ON b.SLS_CONT_NO = SLS.SLS_CONT_NO JOIN MARKETER M ON M.MKTRINIT = SLS.MKTRINIT WHERE b.INVNO = '67883' AND b.CUST_ID = '14714') ELSE (SELECT DISTINCT M.NAME FROM GL c JOIN TANK TA ON c.DEST_TANK = TA.TANK_NO JOIN MARKETER M ON M.MKTRINIT = TA.MKTRINIT WHERE c.INVNO = '67883' AND c.CUST_ID = '14714') END AS NAME FROM GL a WHERE a.INVNO = '67883' AND a.CUST_ID = '14714';

Each subquery must return one row. Otherwise your query will not run.

Parfait · Accepted Answer · 2017-08-14 13:25:52Z

Consider a UNION query using EXISTS/NOT EXISTS clauses where at least one of the SELECT statements will return:

SELECT M.NAME FROM GL JOIN SALES_CONT SLS ON GL.SLS_CONT_NO = SLS.SLS_CONT_NO JOIN MARKETER M ON M.MKTRINIT = SLS.MKTRINIT WHERE GL.INVNO = '67883' AND GL.CUST_ID = '14714' AND EXISTS (SELECT SLS_CONT_NO FROM GL WHERE GL.INVNO = '67883' AND GL.CUST_ID = '14714') UNION SELECT M.NAME FROM GL JOIN TANK TA ON GL.DEST_TANK = TA.TANK_NO JOIN MARKETER M ON M.MKTRINIT = TA.MKTRINIT WHERE GL.INVNO = '67883' AND GL.CUST_ID = '14714' AND NOT EXISTS (SELECT SLS_CONT_NO FROM GL WHERE GL.INVNO = '67883' AND GL.CUST_ID = '14714')

UNION returns a set of unique records, so DISTINCT is unnecessary.
@APC...Correct. I kept the OP's original query without looking carefully.

Collectives™ on Stack Overflow

Conditional joining in oracle sql with if else or not

2 Answers 2

1 Comment

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Linked

Related