I have the following script. I keep getting a divide by 0 error because the second case statement doesn't seem to be working. Can anyone offer any help?? I'm using sql 2005
select
oe_line.unit_price, oe_line.commission_cost, inventory_supplier.cost,
CASE inventory_supplier.cost when 0 then 0 else
(((100 * inventory_supplier.cost * 1.1) / (100.00000001 - CASE oe_line.unit_price WHEN 0 THEN 1 --avoid divide by 0 error ELSE (oe_line.unit_price - oe_line.commission_cost) / oe_line.unit_price * 100 END ) ) - (inventory_supplier.cost * 1.1)) / ((100 * inventory_supplier.cost * 1.1) / (100.00000001 -
CASE oe_line.unit_price WHEN 0 THEN 1 --avoid divide by 0 error ELSE
-- the error occurs in the statement below (oe_line.unit_price - oe_line.commission_cost) / oe_line.unit_price * 100 END ) ) * 100 end c_new_PP, (oe_line.unit_price - oe_line.commission_cost)
FROM oe_line
