oracle query if else

Question

How the below query handle in oracle database, I am trying in Toad

Query1 - SELECT T_BASIS_ACCESS_ID FROM ECKERNEL_MCA.T_BASIS_ACCESS WHERE ROLE_ID LIKE 'MCA.GFS.LEAD'
Query2 - SELECT OBJECT_ID, NAME  FROM ECKERNEL_MCA.OV_AREA WHERE END_DATE IS NULL AND OBJECT_ID  IN
         (SELECT DISTINCT REPLACE(REPLACE(REPLACE(ATTRIBUTE_TEXT, '(', '' ),')',''), '''', '') 
         FROM ECKERNEL_MCA.T_BASIS_OBJECT_PARTITION WHERE T_BASIS_ACCESS_ID IN 
         (SELECT T_BASIS_ACCESS_ID FROM ECKERNEL_MCA.T_BASIS_ACCESS WHERE ROLE_ID LIKE 'MCA.GFS.LEAD') )
Query3 - SELECT OBJECT_ID, NAME  FROM ECKERNEL_MCA.OV_AREA WHERE END_DATE IS NULL

A better explanation would be nice. What is your expected outcome? Provide us some sample data? What is it you are trying to do? Also, it looks like you already tried to ask this question earlier: https://stackoverflow.com/questions/50179087/invalid-sql-statement-in-toad-oracle-db — Patrick H, May 04 '18 at 20:19
I want like if Query1 returns any results from the table , like number of records is 1+ then execute Query2 , if record is zero execute Query 3 if (Query1 > 0) execute Query2 else execute Query3 I am new to oracle , I dont know how to do that.. — Jayesh, May 04 '18 at 20:26
Update the question with the information in your comment above. — Anjan Biswas, May 06 '18 at 04:53

score 0 · Answer 1 · answered May 04 '18 at 20:38

Try this out. It should act the way you indicated in your comment.

I want like if Query1 returns any results from the table , like number of records is 1+ then execute Query2 , if record is zero execute Query 3 if (Query1 > 0) execute Query2 else execute Query3

--Query 1
with Q_1 as
(SELECT T_BASIS_ACCESS_ID FROM ECKERNEL_MCA.T_BASIS_ACCESS WHERE ROLE_ID LIKE 'MCA.GFS.LEAD')

--Query 2
SELECT OBJECT_ID, NAME  FROM ECKERNEL_MCA.OV_AREA WHERE END_DATE IS NULL AND OBJECT_ID  IN
     (SELECT DISTINCT REPLACE(REPLACE(REPLACE(ATTRIBUTE_TEXT, '(', '' ),')',''), '''', '') 
     FROM ECKERNEL_MCA.T_BASIS_OBJECT_PARTITION WHERE T_BASIS_ACCESS_ID IN 
     (SELECT T_BASIS_ACCESS_ID FROM ECKERNEL_MCA.T_BASIS_ACCESS WHERE ROLE_ID LIKE 'MCA.GFS.LEAD') )
--If Query 1 returns anything
WHERE EXISTS (SELECT 1 FROM Q_1)

UNION ALL

--Query 3
SELECT OBJECT_ID, NAME  FROM ECKERNEL_MCA.OV_AREA WHERE END_DATE IS NULL
--If Query 1 returns nothing
WHERE NOT EXISTS (SELECT 1 FROM Q_1)

oracle query if else

1 Answers1