CODEWITHSUNDEEP
sqloracle-database
Shahbaz Ali
Shahbaz Ali

Reputation: 81

How to return null when not matched (Opposite of nullif function)

I want to get null when both exp are not matched like nullif(exp1,exp2) will return null when same and return first exp when not same.

i have tried with the help of sub query or creating functions but need a simple, easy, standard way out. 

select * from table
where service_type = 'X3' 
or (1 <> (select nvl(min(1),2) from table where service_type = 'X3') 
and service_type is null)

This query returns my desire result but i need not to use a sub query.

table have data like

service_type
------------
X4
X3
null

I want to get if service_type match X3 value returns X3 but if not matched like X5 then return null to match null record.

When service_type = 'X3' 

service_type
------------
X3

When service_type = 'X5' or any other like 'X%' will return null

service_type
------------
null

Upvotes: 1

Views: 494

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271241

I think you need a subquery for the logic you actually want to implement:

select t.*
from t
where t.service_type = :value or
      (t.service_type is null and
       not exists (select 1 from t t2 where t2.service_type = :value)
      );

You can also use window functions:

select t.*
from (select t.*, count(*) over (partition by service_type) as cnt
      from t
      where service_type = :value or service_type is null
     ) t
where service_type = :value or
      (cnt = 0 and service_type is null);

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32031

what about case when

select case when service_type='x3' then service_type else null end
from table_name

Upvotes: 1

Related Questions