[thelist] SQL CASE
Phil Turmel
pturmel-webdev at turmel.org
Fri Sep 26 07:08:01 CDT 2008
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.
More information about the thelist
mailing list