Multiple CASEs - syntax

Question

Disclaimer: it is probably a trivial mistake.

To solve the query at this link:

Can you return the results with a column named sound that returns "talk" for humans, "bark" for dogs, and "meow" for cats?

I need to write a CASE with multiple possibilities.

What is wrong with the following syntax?

SELECT *
CASE 
    WHEN species='human' THEN 'talk' 
    WHEN species='dog' THEN 'bark'
    WHEN species='cat' THEN 'meow'
  END AS sound
FROM friends_of_pickles
;

I double-checked the syntax bar reading this question and it seems correct to me?

Where is the mistake?

Thank you very much in advance!

Gordon Linoff · Accepted Answer · 2018-10-07 11:33:42Z

2

Well, you are missing a comma after *:

SELECT *,
       (CASE WHEN species = 'human' THEN 'talk' 
             WHEN species = 'dog' THEN 'bark'
             WHEN species = 'cat' THEN 'meow'
        END) AS sound
FROM friends_of_pickles;

However, some databases do not allow * with other columns. So a qualified * is required:

SELECT fop.*,
       (CASE WHEN species = 'human' THEN 'talk' 
             WHEN species = 'dog' THEN 'bark'
             WHEN species = 'cat' THEN 'meow'
        END) AS sound
FROM friends_of_pickles fop;

To be honest, I recommend a qualifying column names (and *s) in general.

answered Oct 7, 2018 at 11:33

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Carsten Massmann Over a year ago

And the case clause can be shortened to CASE species WHEN 'human' THEN 'talk' WHEN 'dog' THEN 'bark' WHEN 'cat' THEN 'meow' END AS sound.

Collectives™ on Stack Overflow

Multiple CASEs - syntax

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related