CODEWITHSUNDEEP
mysql
HyderA
HyderA

Reputation: 21371

Select case when query not working

I am not sure how cases work with mysql, can anyone explain what's wrong with this query?

CASE WHEN SELECT COUNT(*) from websites where website_id = 171 and master = 2 > 0
SELECT timestamp from websites where website_id = 171 and master = 2 
ELSE SELECT NOW() as timestamp

Upvotes: 0

Views: 336

Answers (3)

cwallenpoole
cwallenpoole

Reputation: 82028

Check out MySQL Control Flow Functions

CASE...WHEN needs to have a THEN keyword to work.

You probably want:

CASE 
    WHEN (SELECT COUNT(*) from websites where website_id = 171 and master = 2) > 0
      THEN (SELECT timestamp from websites where website_id = 171 and master = 2) 
    ELSE NOW()
END as timestamp

If there are only two possibilities, though, you're better with IF:

IF((SELECT count(*) from websites where website_id = 171 and master = 2) > 0,  
   (SELECT timestamp from websites where website_id = 171 and master = 2),  
   NOW()) AS TIMESTAMP

Or, you could use IFNULL and skip the count(*)

IFNULL((SELECT timestamp from websites where website_id = 171 and master = 2),
   NOW()) AS TIMESTAMP

Upvotes: 2

Tudor Constantin
Tudor Constantin

Reputation: 26861

I think you could re-write your query like:

SELECT COALESCE(timestamp, NOW()) from websites where website_id = 171 and master = 2

Upvotes: 1

Bohemian
Bohemian

Reputation: 425003

When using embedding selects to get values, you need to wrap the select in brackets. Also, CASE needs to be closed with an END.
Try this:

CASE
  WHEN (SELECT COUNT(*) from websites where website_id = 171 and master = 2) > 0
    THEN (SELECT timestamp from websites where website_id = 171 and master = 2)
  ELSE (SELECT NOW())
END as timestamp

Upvotes: 0

Related Questions