Using if else blocks getting missing right parenthesis error

Question

I'm new to Oracle and having knowledge of MS SQL. I'm trying to get a phone number depending upon the user_id from Table2 and here is the business logic:

• Case1: if a single match is found in Table1 then get it's respective toll free number from Table2

• Case2: if no match is found in Table1 then get the default toll free number from Table2

• Case3: if an multiple match is found in Table1 then for all those assigned_care_levels get the Care value from Table2 ordered by asc or desc and select the top row phone number.

I wrote the following query which works fine when I run it individually. However, when I cobine it using the if else statements I'm getting the following error ERROR: ORA-00907: missing right parenthesis. Here is my code:

if ((select count(distinct care_level) from Table1 where user_id = '100') > 0)
    select phone from Table2 where care_level in (select distinct care_level from Table1 where user_id = '100')
    and rownum = 1 
    order by care_level asc
else if((select count(distinct care_level) from Table1 where user_id = '100') = 0)
    select phone from Table2 where care_level = 'default'

Answer 1

SET SERVEROUTPUT ON;

DECLARE
       v_CARE_COUNT NUMBER := 0;
       v_PHONE VARCHAr2(40) := NULL;
BEGIN
      select  count(distinct care_level) 
         into v_CARE_COUNT
      from Table1 
       where user_id = '100';

      IF(v_CARE_COUNT > 0) THEN

         select phone into v_PHONE 
         from Table2 
         where care_level in 
           (select distinct care_level from Table1 where user_id = '100')
         and rownum = 1;
      ELSE
         select phone into v_PHONE
          from Table2
         where care_level = 'default';
      END IF;
      DBMS_OUTPUT.PUT_LINE('PHONE is <'||v_PHONE||'>');
 EXCEPTION
 WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Exception : '||SQLERRM);
 END;
 /

EDIT: ( AS SIngle SQL)

 SELECT PHONE 
 FROM TABLE2
 WHERE CARE_LEVEL in
       (
          SELECT CARE_LEVEL FROM TABLE1 WHERE USER_ID='100'
           UNION
          SELECT CARE_LEVEL FROM TABLE1 WHERE CARE_LEVEL='default'
            AND NOT EXISTS
              (SELECT 'X' FROM TABLE1 WHERE USER_ID='100')
        )
 AND ROWNUM = 1;

Answer 2

The 'else if' syntax is wrong. In Oracle you need to use 'ELSIF' and complete the whole conditional statement with an 'END IF'. The SQL statements within should also be followed with a ;

Try:

IF ((select count(distinct care_level) from Table1 where user_id = '100') > 0) THEN
    select phone from Table2 where care_level in (select distinct care_level from Table1 where user_id = '100')
    and rownum = 1 
    order by care_level asc;
ELSIF ((select count(distinct care_level) from Table1 where user_id = '100') = 0) THEN
    select phone from Table2 where care_level = 'default'; END IF