CODEWITHSUNDEEP
sqloracle-database
user2967948
user2967948

Reputation: 549

SQL - Where Clause to Use Case Logic

I need to use a case type of logic in my query. Database Used: Oracle 11g

The present query looks like:

SELECT name 
FROM merchant m 
WHERE NOT EXISTS ( 
   SELECT 1 
   FROM settlement s 
   WHERE s.sid = m.mid 
   AND s.rpt_id IN ('111','112','202')
)

There is another column SRID which takes precedence over SID and which can as well be mapped to MID. Now, we need something like IF SRID is null then use SID, else use SRID to map to MID in the WHERE clause.

Upvotes: 1

Views: 147

Answers (3)

John C
John C

Reputation: 238

I think you want the coalesce function, which returns the first non-null value. Unfortunately I don't have access to an Oracle system to confirm.

select NAME 
from merchant m 
where NOT EXISTS (
    SELECT 1 from SETTLEMENT s 
    where coalesce(s.SRID,s.SID) = m.MID and 
    s.RPT_ID IN ('111','112','202')
    )

Here is a comparison between coalesce and nvl. (I was not familiar with nvl)

Upvotes: 3

Dba
Dba

Reputation: 6639

Try with NVL function.

SELECT NAME 
FROM   merchant m 
WHERE  NOT EXISTS(
     SELECT 1 
     FROM settlement s 
     WHERE NVL(s.srid, s.sid) = m.mid 
     AND s.rpt_id IN ('111','112','202'));

Upvotes: 0

TechDo
TechDo

Reputation: 18629

Please use

NVL(s.SRID, s.SID) = m.MID

instead of

s.SID = m.MID

in the where condition.

Upvotes: 0

Related Questions