+ -
当前位置:首页 → 问答吧 → case when 为空时如何处理

case when 为空时如何处理

时间:2011-12-28

来源:互联网

SELECT CASE
  WHEN '' IS NULL THEN 222
  ELSE 0
  END
  FROM DUAL;

这条语句可以正常执行 ,但是下面这条语句就无法正常执行了,请大虾指点

(case receive_detail.USER_DEFINED_03 --列名
  when 'NORMAL' then 'Good'
  when 'BAD' then 'Damaged'
  when '' is null then '/'
  else receive_detail.USER_DEFINED_03
  end) as status,

作者: logive   发布时间: 2011-12-28

(case receive_detail.USER_DEFINED_03 --列名
  when 'NORMAL' then 'Good'
  when 'BAD' then 'Damaged'
  when null then '/'
  else receive_detail.USER_DEFINED_03
  end) as status

作者: rfb0204421   发布时间: 2011-12-28

引用 1 楼 rfb0204421 的回复:

(case receive_detail.USER_DEFINED_03 --列名
when 'NORMAL' then 'Good'
when 'BAD' then 'Damaged'
when null then '/'
else receive_detail.USER_DEFINED_03
end) as status

但是这样达不到想要的效果 ,即空值还是没有被替换成 ‘/’

作者: logive   发布时间: 2011-12-28

SQL code
(case when receive_detail.USER_DEFINED_03 ='NORMAL' then 'Good'
when receive_detail.USER_DEFINED_03 ='BAD' then 'Damaged'
when receive_detail.USER_DEFINED_03 is null then '/'
else receive_detail.USER_DEFINED_03
end) as status

作者: xpingping   发布时间: 2011-12-28

引用 3 楼 xpingping 的回复:

SQL code
(case when receive_detail.USER_DEFINED_03 ='NORMAL' then 'Good'
when receive_detail.USER_DEFINED_03 ='BAD' then 'Damaged'
when receive_detail.USER_DEFINED_03 is null then '/'
else receive_d……

这个写法 运行时会报错:ORA-00905: missing keyword

作者: logive   发布时间: 2011-12-28

SQL code

--为何不用decode函数呢?
decode(receive_detail.USER_DEFINED_03,'NORMAL','Good',
       'BAD','Damaged',
       '','/',
       receive_detail.USER_DEFINED_03) as status

作者: BOBO12082119   发布时间: 2011-12-28

不会吧,我这个试过了,行的啊
SQL code
with a as(select '1' d from dual union all
select '2'  from dual union all
select '5'  from dual union all
select ''  from dual 
)
select case when d='1' then '11' 
when d='2' then '22' 
when d is null then '/' 
else d end from a
——————————————————————————
11
22
5
/

作者: xpingping   发布时间: 2011-12-28

热门下载

更多