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;