MySQL Select Case

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
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'

WHEN event Is Null THEN venue
End as 'List'
,DATE_FORMAT(fld_date, '%M') as Monthly  
Quoted text here. Click to load it

Re: MySQL Select Case wrote:
Quoted text here. Click to load it

  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

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.


Re: MySQL Select Case

vncntj wrote:

Quoted text here. Click to load it
Quoted text here. Click to load it


Toby A Inkster BSc (Hons) ARCS
Contact Me  ~

Site Timeline