user3619389 user3619389 - 4 months ago 12
SQL Question

is there a way to check if a value is zero and do this or do that in mysql

I want to do an sql query

SELECT * from members m where abs(due/30*service_charge)>='0'
AND abs(due/30*service_charge)<='200' group by id


But in some cases the value of service_charge will 0 , so i want to write it like if service_charge = 0 , then service_charge = 1;

Is there a way for this ?.

Answer

Use case:

select *
from members m
where abs(due/30*(case when service_charge = 0 then 1 else service_charge end) >= 0 and
      abs(due/30*(case when service_charge = 0 then 1 else service_charge end)<= 200
group by id;

However, this is typically handled using NULLIF() -- meaning that NULL would be returned from the calculation:

select *
from members m
where abs(due / 30 * nullif(service_charge, 0) >= 0 and
      abs(due / 30 * nullif(service_charge, 0) <= 200
group by id;

Also, don't use single quotes around numeric constants. This confuses people and could confuse the SQL optimizer.