Reputation: 59
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
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
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