IF NOT EXISTS in Oracle-CodePudding

I am new in Oracle, need some help to SQL Server's IF NOT EXISTS equivalent in Oracle. I need to find the max RoleID from Role table based on entity number if a particular role does not exists. I have created below query but its failing (it should return null if an entity has that particular role and should return 1 if an entity has no role, its returning 1 in both cases) if an entity does not have any role.

Code:-

SELECT NVL(MAX(role_id), 0)   1 AS RoleID from roles WHERE entity_no = '000001'
AND
    NOT EXISTS (
        SELECT 1
        FROM roles
        WHERE entity_no = '000001' AND name = 'Survey'
    )

I need 1 as RoleID if an entity does not have any role(s) but it should return null for the entity having that particular role('Survey') else return max RoleID with increment, TIA.

CodePudding user response：

Would this do? Read comments within code.

entity_no = 00001 has Survey, so query should return NULL
entity_no = 00002 doesn't have Survey, so query should return 1

SQL> with
  2  roles (role_id, entity_no, name) as
  3  -- sample data
  4    (select 1, '00001', 'Survey' from dual union all
  5     select 2, '00002', 'xxx'    from dual
  6    ),
  7  temp as
  8  -- does ENITITY_NO has role for NAME = Survey? If so, CNT = 1; else, CNT = 0
  9    (select entity_no,
 10            sum(case when name = 'Survey' then 1 else 0 end) cnt
 11     from roles
 12     group by entity_no
 13    )
 14  -- finally, check CNT value and return the result
 15  select case when t.cnt = 0 then 1 else null end as role_id
 16  from roles r join temp t on t.entity_no = r.entity_no
 17  where r.entity_no = '&par_entity_no';
Enter value for par_entity_no: 00001

   ROLE_ID
----------


SQL> /
Enter value for par_entity_no: 00002

   ROLE_ID
----------
         1

SQL>