hi all,

i want to make following:

field1 + field2 + field3

but only when these fields were not NULL !

i tried as follows:

......
CASE WHEN b.F1 IS NULL THEN 0 ELSE b.F1 END,
CASE WHEN b.F2 IS NULL THEN 0 ELSE b.F2 END,
CASE WHEN b.F3 IS NULL THEN 0 ELSE b.F3 END,
(b.F1+b.F2+b.F3) as XXX, b.F1, b.F2, b.F3,.....

the result column XXX is NULL for each row.

what to to ?

does this help ?


CASE WHEN b.F1 IS NULL THEN 0 ELSE b.F1 END+
CASE WHEN b.F2 IS NULL THEN 0 ELSE b.F2 END+
CASE WHEN b.F3 IS NULL THEN 0 ELSE b.F3 END as XXX,
b.F1, b.F2, b.F3

Sathyaram

...and how it does :-)

thx a lot - you 're great!

You can also use coalesce:


COALESCE(b.F1,0)+coalesce(b.F2,0)+coalesce(b.F3,0) as XXX,


Makes it a litter easier to read.

Andy