CODEWITHSUNDEEP
mysql
vikrant Verma
vikrant Verma

Reputation: 498

How to apply if else in sql query

i have written a sql query to fetch product now i want to apply a where clause in my query but for where i have there condition

if stock = 'in':
 where will be num>0:

if stock = 'out':
where will be num = 0;
 if stock = ' ':
no where clause will apply

i write this for two condtions

where       
   case 
      when stock='in' then num_in_stock > 0
      when stock ='out' then num_in_stock = 0
   end;

but it is not working. I am getting an error:

(1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; as a where a.partner_id = 5 order by a.date_updat' at line 20") I am getting stock variable by 

stock = request.GET.get('stock')

My full query is

 SELECT distinct a.product_id, a.variant_id 
    from 
    (
        SELECT cp.id as variant_id,
        COALESCE(cp.parent_id,cp.id) as product_id,
        ps.partner_id as partner_id,
        ps.price_retail as price_retail,
        COALESCE(oc.order_count,0) as order_count,
        cp.date_updated as date_updated
        FROM partner_stockrecord as ps 
        LEFT JOIN catalogue_product as cp on cp.id = ps.product_id 
        LEFT JOIN (
            select product_id,count(product_id) as order_count 
            from order_line group by product_id) as oc on oc.product_id = ps.id 
            where ((''' + stock +''') = 'in' and num_in_stock > 0 ) or
                ((''' + stock +''') = 'out' and num_in_stock = 0 );


        ) 
    as a where a.partner_id = '''+ str(partner_obj.id) +''' order by a.date_updated desc;
    ''');

Upvotes: 1

Views: 56

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

Convert:

 where 
    case 
       when stock='in' then num_in_stock > 0
       when stock ='out' then num_in_stock = 0
    end

into:

where (stock = 'in' and num_in_stock > 0 ) or
      (stock = 'out' and num_in_stock = 0 )

CASE is an expression that simply returns a scalar value. It cannot be used to control flow of execution in SQL.

Upvotes: 5

Related Questions