Else if condition using mysql CASE not working?

Question

I have column location but i need to return this column based on value of another column below is the MYSQL SELECT QUERY:

if(block.block_id IS NOT NULL)
{
 location.location = 'BLOCKED'
}elseif(nvocc.nvocc_id is not null AND nvocc.nvocc_status = 'IN') {
  location.location = 'NVOCC'
}else {
  location.location = location.location
}

i treid with case conditon but its showing error

CASE 
     WHEN block.block_id IS NOT NULL THEN "BLOCKED"
     WHEN ((nvocc.nvocc_id IS NOT NULL) AND (nvocc.nvocc_status='IN')) THEN "NVOCC"
     ELSE location.location
END as location

the above query returns nothing and not working please someone help me to solve this issue.

possible duplicate of How do write IF ELSE statement in a MySQL query — Styphon
– Styphon, Commented Aug 28, 2015 at 13:03
A query in MySQL starts with SELECT, not with CASE. It is entirely unclear what code you are actually running. — Gordon Linoff
– Gordon Linoff, Commented Aug 28, 2015 at 13:34

Thomas · Accepted Answer · 2015-08-28 13:07:55Z

1

Try using parenthesis to clearly separate your condition block from control flow block:

CASE 
     WHEN block.block_id IS NOT NULL THEN "BLOCKED"
     WHEN ((nvocc.nvocc_id IS NOT NULL) AND (nvocc.nvocc_status='IN')) THEN "NVOCC"
     ELSE location.location
END

Try simplifying your query until it works and then progressively add conditions.

And please provide your error, it's really hard to guess.

answered Aug 28, 2015 at 13:07

Thomas

637 bronze badges

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

2 Comments

Jonathan John Over a year ago

Query is excuted but i there is no change in output

Thomas Over a year ago

please provide the full request, it is hard to know else.

Collectives™ on Stack Overflow

Else if condition using mysql CASE not working?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related