Check NULL on a column and return result

Question

hey this is my requirment.

i need to check whether a column is null if so i need to return 'MISSING' else empty sting.

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

te result should be

COLUMN1  RESULT
P1         '' 
P2        MISSINGCOLUMN2
P3        MISSINGCOLUMN3,COLUMN2

I tried using CONCAT ('MISSING', NVL(COLUMN2,'COLUMN2')) - its working if the value is NULL but in case of valid value it is retirning the valid value; because we cant pass 3 parameters to the NVL statement.

Do you realize that, in Oracle, the empty string is exactly the same as `NULL`? Other databases treat the empty string as a valid non-NULL value, Oracle does not. http://stackoverflow.com/questions/203493/why-does-oracle-9i-treat-an-empty-string-as-null/203536#203536 So do you want the `result` for `p1` to be `NULL`? — Justin Cave, Sep 04 '13 at 21:14
Yes. the reason is after loading this final table i will pull LENGTH(RESULT)> 1 and mark entry to the Error Logging table — Ragav, Sep 04 '13 at 21:17

score 0 · Answer 1 · answered Sep 04 '13 at 21:26

0

You might try something like

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

answered Sep 04 '13 at 21:26

JJG

1
1

Thanks JJG i tried this DECODE(COLUMN1, NULL, 'missingCOLUMN1','') let me know if this also fine? – Ragav Sep 04 '13 at 21:35

score 0 · Answer 2 · answered Sep 04 '13 at 21:29

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

answered Sep 04 '13 at 21:29

Harshit

560
1
5
15

Thanks harshit, i tried this DECODE(COLUMN1, NULL, 'missingCOLUMN1','') – Ragav Sep 04 '13 at 21:50
what about the other conditions?? – Harshit Sep 04 '13 at 21:52
DECODE(COLUMN1, NULL, 'missingCOLUMN1','') || DECODE(COLUMN2, NULL, 'missingCOLUMN2','') i will use the same condition for all the columns – Ragav Sep 04 '13 at 22:11

score 0 · Answer 3 · answered Sep 04 '13 at 22:02

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

Check NULL on a column and return result

3 Answers3