how do I use IF exists and else if condition based on row returns in Oracle-CodePudding

I have to write a conditional statements based on record returns in oracle

something like as below:

if exists (select s,d,r from MyTable where s_id = '1')
select 's' || s || ',d' || d || ',r' || r as result from MyTable;
else
if exists (select s,d,r from MyTable where d_id = '1')
select 's00000,d' || d || ',r' || r as result from MyTable;
else
if exists (select s,d,r from MyTable where r_id = '1')
select 's00000,d000,r' || r as result from MyTable;
else
select 's00000,d000,r00' as result from dual

I do not think 'if exists' command is in oracle. How do I do something similar in Oracle?

CodePudding user response：

You can use a CASE expression

SELECT DISTINCT
    CASE
        WHEN exists (select * from MyTable where s_id = '1')
        THEN 's' || s || ',d' || d || ',r' || r
        WHEN exists (select * from MyTable where d_id = '1')
        THEN 's00000,d' || d || ',r' || r
        WHEN exists (select s,d,r from MyTable where r_id = '1')
        THEN 's00000,d000,r' || r
        ELSE 's00000,d000,r00'
    END AS result
FROM
   MyTable

However, I am not sure whether this is what you need. The expression exists (select * from MyTable where s_id = '1') tests whether at least one s_id = '1 exists in the whole table and then applies the same expression for all records in the table. Probably you want to evaluate the values and decide which expression to use on per record basis (at least this makes more sense to me and is, btw., much faster):

SELECT
    CASE
        WHEN s_id = '1' THEN 's' || s || ',d' || d || ',r' || r
        WHEN d_id = '1' THEN 's00000,d' || d || ',r' || r
        WHEN r_id = '1' THEN 's00000,d000,r' || r
        ELSE 's00000,d000,r00'
    END AS result
FROM
   MyTable

In the first query the DISTINCT keyword is required to eliminate duplicates of 's00000,d000,r00'.

SQL is optimized to perform set operations very efficiently whereas procedural statements are not as fast. Therefore, do not think procedurally when writing SQL.