Re: Check for NULL in CASE

cubeberg — Fri, 19 May 2006 19:27:30 GMT

Have you considered using Coalesce? Coalesce(Note1 + DrAccount, Location + DrAccount)

COALESCE

Returns the first nonnull expression among its arguments.

Syntax

COALESCE ( expression [ ,...n ] )

Check for NULL in CASE

sunstar — Fri, 19 May 2006 18:53:40 GMT

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 ???

SQL Server, SQL Server Express, and SqlDataSource Control

Re: Check for NULL in CASE

COALESCE

Syntax

Check for NULL in CASE