CASE statement

[cupboy]

I'm trying to figure out how to use the CASE statement:

My query:

select artist,disc_title,disc_catno,label_name,CASE qty WHEN ISNULL(qty)=1 THEN '1' ELSE qty END CASE ,format,disc_seq from cd_data d, cd_label l, cd_artis a where d.disc_label = l.label_no and d.artist_no = a.artist_no and d.artist_no =237 order by 1,2

Format from MySQL 5.0 Reference Manual

CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE

My question: How do I change my query to match up with the reference manual format? It would sure be nice to have an example in the manual.

In the meantime I will use:

select artist,disc_title,disc_catno,label_name,
IF (ISNULL(qty)=1,'0',qty),

[Lars_Berg]

You are looking at the wrong place in the manual. You should look for case expression and not case statement.

select artist,
          disc_title,
          disc_catno,
          label_name,
         CASE qty WHEN qty is null  THEN 1 ELSE qty END,
         format,
        disc_seq 
  from cd_data d, 
          cd_label l, 
          cd_artis a 
   where d.disc_label = l.label_no 
      and d.artist_no = a.artist_no 
     and d.artist_no =237 
  order by artist,disc_title

There is a function for this, i.e.

CASE qty WHEN qty is null  THEN 1 ELSE qty END

is equivivalent to

Coalesce(qty,1)