"case col1 when null then..." not working

[Former Member]

Hello,

I would like to write a simple sql statement, which would calculate weight by checking if values in a table are nulls or different than nulls.

select

    col1, col2,

   (case col1 when null then 0 else 10  end ) + (case col2 when null then 0 else 1  end ) as weight

  from tab1

Here is the output:

COL1;COL2;WEIGHT

a;aa;11

a;ab;11

d;dd;11

d;de;11

?;ee;11

aa;aaaa;11

Has anyone idea how I should write the case statement so that the weight is calculated correctly for all of the cases (here error appears in row number 5)?

Best regards,

Leszek

[Former Member · Accepted solution]

Hi,

Try exceuting,

select

    col1,

    col2,

   ( (case when col1 IS NULL then 0 else 10  end ) + (case when col2 IS NULL then 0  else 1 end )) as  weight

  from tab1

Cheers,

Pp

[Former Member · Accepted solution]

This one works fine. Thanks a lot!

BR,

Leszek