Nested Case Statements

[cbielins]

I have a problem, and would like you input...

I need to evaluate to columns and then base a third column on their
values. But, I need to include NULL values and this statement isn't
working, what am I doing wrong? Is this possible?

Example:

CASE A1
WHEN NULL THEN CASE A2
WHEN NULL THEN 'B1'
ELSE 'B2'
END
ELSE 'B3'
END AS C1
So based on if A1 is NULL or not, continue to the nested CASE
statement. If A1 is NOT NULL then assign C1 = B3, but if A1 is NULL AND
A2 is NULL then assign C1 = B1, else assign C1 = B2.

Understand?? Confused??

Any help would be greatly appreciated.
Thanks,
Chris

[lennart]

cb******@gmail.com wrote:

I have a problem, and would like you input...

I need to evaluate to columns and then base a third column on their
values. But, I need to include NULL values and this statement isn't
working, what am I doing wrong? Is this possible?

Example:

CASE A1
WHEN NULL THEN CASE A2
WHEN NULL THEN 'B1'
ELSE 'B2'
END
ELSE 'B3'
END AS C1

I would suggest the other possible case construction:

CASE WHEN A1 IS NULL
THEN CASE WHEN A2 IS NULL
THEN 'B1'
ELSE 'B2'
END
ELSE 'B3'
END AS C1

HTH
/Lennart


So based on if A1 is NULL or not, continue to the nested CASE
statement. If A1 is NOT NULL then assign C1 = B3, but if A1 is NULL AND
A2 is NULL then assign C1 = B1, else assign C1 = B2.

Understand?? Confused??

Any help would be greatly appreciated.
Thanks,
Chris

[Brian Tkatch]

Not an answer, just another way of doing it: COALESCE(A1 || 'B3', A2 ||
'B2', 'B3')

I wonder which is faster.

B.

[cbielins]

Thanks Lennart.... works beautifully!

+CB

[Knut Stolze]

Brian Tkatch wrote:

Not an answer, just another way of doing it: COALESCE(A1 || 'B3', A2 ||
'B2', 'B3')

This won't work as || is the concatenation operator, and if A1 is indeed
NULL, the concatenation will be NULL and the other parameters of COALESCE
are considered. But if A1 is not NULL, the result is the concatenation of
A1 with 'B3', and that's different from just 'B3'. So you have to resort
to the CASE expressions.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Brian Tkatch]

Heh. I was concentrating on it returning NULL to the COALESCE, i was
treating the not-NULL value as a zero-length string. Thanx for the
catch.

Hmm.. shouldn't wrapping that in RIGHT() should take care of that:

CHAR(RIGHT(COALESCE(A1 || 'B3', A2 || 'B2', 'B3'), 2), 2)

What i'm really curious about is if there is a preference to CASE or a
built-in FUNCTION when it comes to performance.

B.