1

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.

user2967948
  • 529
  • 2
  • 8
  • 23

3 Answers3

3

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)

Community
  • 1
  • 1
John C
  • 238
  • 2
  • 8
0

Please use

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

instead of

s.SID = m.MID

in the where condition.

TechDo
  • 18,398
  • 3
  • 51
  • 64
0

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'));
Dba
  • 6,511
  • 1
  • 24
  • 33