[thelist] SQL CASE

Chrome admin at chrome.me.uk
Fri Sep 26 08:13:19 CDT 2008



> -----Original Message-----
> From: thelist-bounces at lists.evolt.org [mailto:thelist-
> bounces at lists.evolt.org] On Behalf Of Chris Anderson
> Sent: 26 September 2008 11:57
> To: thelist at lists.evolt.org
> Subject: Re: [thelist] SQL CASE
> 
> > 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

That one works! Thanks for that, very much appreciated

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

Cheers also to Phil... A few things have been cleared up for me a little
here and I really appreciate the pointers

SQL is an area where I definitely need to do a lot of reading but this was
the first time I'd needed a conditional within a statement

If it helps anyone else this was a MySQL DB

Again thanks and Friday beers all round I think (to be had outside as it's a
rare sunny day here in Wales :) )

Dan






More information about the thelist mailing list