[thelist] SQL CASE

[Phil Turmel]

Hi Chrome,

Chrome wrote:
> Hi all
> 
> This is probably something stupid that I've done and can't see... I'm trying
> to use a CASE statement to detect a null value and return a value based on
> it
> 
> 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';

Problem #1: Yes, drop the semicolons.

>    END CASE
          ^^^^
Problem #2.  This doesn't belong.  And you might need 'AS colname', 
depending on your SQL flavor.

Possible Problem #3:  You usually can't do a bare compare against 
NULL.  You typically need the 'IS NULL' syntax.  I recommend the 
alternate CASE syntax in this specific situation:

CASE WHEN logo IS NULL
   THEN 'This is NULL'
   ELSE 'This is not NULL'
END
AS NoLogo

> 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?
> 
> Cheers in advance
> 
> Dan (who's really glad it's Friday)
> 

HTH,

Phil
-- 
Need to contact me offlist?
   Drop -webdev or you probably won't get through.