# Thread: Probs with case expression

1. Registered User
Join Date
Apr 2004
Posts
2

## Unanswered: Probs with case expression

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 ?

2. Super Moderator
Join Date
Aug 2001
Location
UK
Posts
4,650

## Re: Probs with case expression

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

Originally posted by ulirebmann
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 ?

3. Registered User
Join Date
Apr 2004
Posts
2

## Re: Probs with case expression

Originally posted by sathyaram_s
does this help ?
Sathyaram

...and how it does :-)

thx a lot - you 're great!

4. Registered User
Join Date
Jan 2003
Posts
4,310
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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•