MySQL Select Case

[vncntj]

I'm trying to get the venue in the 'List' column when in contains a
value. But if the venue Is Null then place the event in the 'List'

SELECT
CASE
WHEN event Is Null THEN venue
Else
event
End as 'List'
,DATE_FORMAT(fld_date, '%M') as Monthly

From na_statistics

[rlee0001]

vn****@hotmail.com wrote:

I'm trying to get the venue in the 'List' column when in contains a
value. But if the venue Is Null then place the event in the 'List'

SELECT
CASE
WHEN event Is Null THEN venue
Else
event
End as 'List'
,DATE_FORMAT(fld_date, '%M') as Monthly

From na_statistics

SELECT
COALESCE("event", "venue") AS "list",
DATE_FORMAT("date", '%M') AS "monthly"
FROM "my_namespace"."na_statistics";

So why not use the COALESCE function? Is this not exactly what it is
for? You can have as many values as you want and the database with use
the first non-null value specified. But that said I see nothing wrong
with your syntax for CASE except that the logic does not match your
explaination (you say you want "venue" if available and "event" only
when "venue" isn't; but you are instead checking "event" for null and
using "venue" only then). Also, IF/THEN could have done the same work
as well. Or "IFNULL" (similar to COALESCE but accepting exactly two
parameters).

And just an aside: I'm not sure about MySQL but under PostgreSQL you
_can_ use reserved words in field names as long as they are surrounded
by double-quotes. So fld_date can become just "date" which I think is a
bit more elegant. Besides that I always surround my identifiers in
quotes out of habit. :o) The only thing with that is that when you put
identifiers in quotes IIRC they become case sensitive so I also make it
a habit to use lower case for identifiers all the time. And I guess
other than that it's a good idea to fully qualify table names with the
appropriate namespace. In MySQL that means always specifying the
database in the FROM/JOIN clauses. In PostgreSQL that means always
specifying the schema there.

-Robert

[Toby Inkster]

vncntj wrote:

if the venue Is Null [...] WHEN event Is Null

??

--
Toby A Inkster BSc (Hons) ARCS
Contact Me ~ http://tobyinkster.co.uk/contact