首页 文章

检查列上的NULL并返回结果

提问于
浏览 次
0

嘿，这是我的要求 .

我需要检查列是否为null，如果是这样我需要返回'MISSING'，否则为空sting .

Column1(PrimaryKey)   COLUMN2  COLUMN3
P1                    ABC      DEF
P2                    NULL    KJL
P3                     NULL     NULL

结果应该是

COLUMN1  RESULT
P1         '' 
P2        MISSINGCOLUMN2
P3        MISSINGCOLUMN3,COLUMN2

我尝试使用CONCAT（'MISSING'，NVL（COLUMN2，'COLUMN2'）） - 如果值为NULL则其工作，但如果值为有效，则重新生成有效值;因为我们无法将3个参数传递给NVL语句 .

sql oracle function

3 回答

  • 0
    select column1, CASE
WHEN
Column2 is null and column3 is not null
then
'MISSING COLUM2'
WHEN
Column3 is null and column2 is not null
 then
 'MISSING COLUM3'
 WHEN
Column3 is null and column2 is  null
then
  'MISSING COLUM3, column2'
WHEN
Column3 is not null and column2 is not null
 then
''''
end as result
from table
    回复于
  • 0

    你可能会尝试类似的东西

    select Column1,
   case 
      when Column2 is null and Column3 is null then 'MISSINGCOLUMN3,COLUMN2' 
      when Column2 is null then  'MISSINGCOLUMN2' 
      when Column3 is null then  'MISSINGCOLUMN3' 
      else ' '
   end
from ...
    回复于
  • 0
    SELECT "COLUMN1",
    (case when "COLUMN2" is null or "COLUMN3" is null then 'MISSING' else '' end)
    ||
    (case when "COLUMN3" is null then 'COLUMN3' else '' end)
    ||
    (case when "COLUMN2" is null and "COLUMN3" is null then ',' else '' end)
    ||
    (case when "COLUMN2" is null then 'COLUMN2' else '' end) as RESULT
FROM ...
    回复于

相关问题