CODEWITHSUNDEEP
sqloracle-database
Sunil Bamal
Sunil Bamal

Reputation: 117

IF NOT EXISTS in Oracle

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.

Upvotes: 0

Views: 431

Answers (1)

Littlefoot
Littlefoot

Reputation: 142710

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>

Upvotes: 1

Related Questions