CODEWITHSUNDEEP
mysqlsqlcase
user3733831
user3733831

Reputation: 2926

MySQL alternative way to `CASE` Statement

May I know, is there any alternative way or short way to write this case statement shown in the below:

case 
  when p.our_price IS NULL OR p.our_price = 0 
   then p.sales_price 
  else p.our_price 
end as sales_price

I tried using mysql built in funciton ifnull as below:

ifnull(p.our_price,p.sales_price)

But it doesn't work for me.

Upvotes: 1

Views: 527

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521389

You could stick with a CASE expression but use COALESCE to make it more succinct:

CASE COALESCE(p.our_price, 0)
     WHEN 0 THEN p.sales_price ELSE p.our_price END AS sales_price

Or, using the IF() function:

IF(COALESCE(p.our_price, 0) = 0, p.sales_price, p.our_price)

As a long time MySQL user, I often find myself using CASE more than IF() as the former allows omitting the ELSE condition entirely. IF() on the other hand always requires an explicit else value.

Upvotes: 4

Rinkesh P
Rinkesh P

Reputation: 678

IF(p.our_price IS NULL OR p.our_price = 0, p.sales_price, p.our_price)

the 1st argument is the condition, 2nd and 3rd are the choices in case the condition is true or false.

Upvotes: 3

Related Questions