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 q...

问题:

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

回答1:

what about case when

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

回答2:

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);
  • 发表于 2019-05-19 21:36
  • 阅读 ( 253 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除