Reputation: 81
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
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
Reputation: 32031
what about case when
select case when service_type='x3' then service_type else null end
from table_name
Upvotes: 1