CODEWITHSUNDEEP
sqloracle-database
Ranjith
Ranjith

Reputation: 59

Conditional joining in oracle sql with if else or not

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

Upvotes: 1

Views: 9601

Answers (2)

Parfait
Parfait

Reputation: 107767

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')

Upvotes: 1

Ferdinand Gaspar
Ferdinand Gaspar

Reputation: 2073

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';

Upvotes: 1

Related Questions