SELECT whatever_field,
CASE LEN(DrAccount)
WHEN 12 THEN DrAccount
ELSE CASE (Note1)
WHEN NULL THEN Location + DrAccount
ELSE Note1 + DrAccount
END
END AS Account
FROM Table1
The purpose of the CASE(Note1) is when Note1 column is null, return Location+DrAccount.
The actual result is when Note1 column is null, it always returns null, Location+DrAccount is not executed. When Note1 column is not null, it returns correctly Note1+DrAccount.
The problem seems to reside in validating the null value in
WHEN NULL
How to check for null in CASE(fieldname) WHEN ???