[thelist] SQL CASE

[Chris Anderson]

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