[thelist] SQL CASE
Chris Anderson
Chris at activeide.com
Fri Sep 26 05:57:13 CDT 2008
> Here's the devastatingly simple code I've tried
>
> SELECT
> logo AS logo_url,
> CASE logo
> WHEN NULL THEN 'This is NULL';
> WHEN NOT NULL THEN 'This is not NULL';
> END CASE
> FROM
> jos_affiliateMain
> WHERE
> affiliate_account_id = 1;
>
> I've tried omitting the semicolons from the WHEN statements and I've tried
> using logo_url instead of logo in the CASE bit but all I get are syntax
> errors (really unhelpful ones: error next to '' on line blah)... I've also
> tried things like WHEN '' blah
>
> Can anyone see what I've naffed up? Do I need more caffeine?
>
I don't think the "CASE variable WHEN value" syntax supports checking for nulls
I would just use:
CASE
WHEN logo IS NULL THEN 'This is null'
WHEN logo IS NOT NULL THEN 'Not Null'
END
or even
CASE
WHEN logo IS NULL THEN 'This is null'
ELSE 'Not Null'
END
You may also want to look at the ISNULL() function (assuming SQL Server)
More information about the thelist
mailing list