title: Informix divide by zero error
body: <p>I have a query which is erroring out with a divide by zero error. I have isolated the part of the query which errors and tried to fix it using an NVL but it's just not going well, can someone spot my error?</p>
  
  <pre><code>select  warehouse.ware_code,
      warehouse.desc_text,
      maingrp.maingrp_code,
      maingrp.desc_text,
      prodgrp.prodgrp_code,
      prodgrp.desc_text,
      left(product.part_code,7) Parent_Product_code,
      product.part_code,
      product.desc_text,
      sum(salesstat.order_qty - salesstat.return_qty) Total_Sales,
      sum((salesstat.order_qty - salesstat.return_qty) / 6) Average_Weekly_Sales,
      sum(((salesstat.order_qty - salesstat.return_qty) / 6) * 4) Average_Monthly_Sales,
      '??' Note,
      sum(prodstatus.onhand_qty) Stock_On_Hand,
      sum(orderstat.order_qty - orderstat.cancel_qty) Outstanding_orders,
      sum(prodstatus.onhand_qty - (orderstat.order_qty - orderstat.cancel_qty)) Stock_Available,
      --CASE
      --WHEN nvl(sum(salesstat.order_qty - salesstat.return_qty),0) = 0
      --  THEN 999
      --ELSE  sum(prodstatus.onhand_qty / ((salesstat.order_qty - salesstat.return_qty) / 6))
      --END as Weeks_Stock_Remaining,
      '??' Stock_ordered,
      '??' Production_date
  from
      warehouse,
      warereptdetl,
      maingrp,
      prodgrp,
      product,
      prodstatus,
      salesstat,
      orderstat,
      Statint
  where   warehouse.cmpy_code = maingrp.cmpy_code
  and warehouse.cmpy_code = warereptdetl.cmpy_code
  and warehouse.cmpy_code = prodgrp.cmpy_code
  and warehouse.cmpy_code = product.cmpy_code
  and warehouse.cmpy_code = salesstat.cmpy_code
  and warehouse.cmpy_code = prodstatus.cmpy_code
  and warehouse.cmpy_code = orderstat.cmpy_code
  and warehouse.cmpy_code = statint.cmpy_code
  and product.ware_code = prodstatus.ware_code
  and product.ware_code = warehouse.ware_code
  and product.ware_code = salesstat.ware_code
  and product.ware_code = orderstat.ware_code
  and product.ware_code = warereptdetl.ware_code
  and product.part_code = salesstat.part_code
  and product.part_code = orderstat.part_code
  and product.part_code = prodstatus.part_code
  and product.prodgrp_code = salesstat.prodgrp_code
  and product.prodgrp_code = orderstat.prodgrp_code
  and product.prodgrp_code = prodgrp.prodgrp_code
  and product.maingrp_code = salesstat.maingrp_code
  and product.maingrp_code = orderstat.maingrp_code
  and product.maingrp_code = maingrp.maingrp_code
  and salesstat.year_num = statint.year_num
  and salesstat.int_num = statint.int_num
  and salesstat.stat_type_code = statint.type_code
  and orderstat.year_num = statint.year_num
  and orderstat.int_num = statint.int_num
  and orderstat.stat_type_code = statint.type_code
  and warehouse.cmpy_code = 'AB'
  and warereptdetl.warereptgrp_code = 'NSW'
  and product.ware_code = 'HP1'
  and product.status_ind &lt;&gt; 3
  and left(product.part_code,2) = '21'
  and statint.year_num = 2016
  and statint.int_num between 29 AND 34
  --and   statint.int_num = 29
  and statint.type_code = 'WLY'
  Group by 1,2,3,4,5,6,7,8,9
  </code></pre>
  
  <p>The commented out part is where I have tried to put in a fix, if I comment it out the query runs fine (albeit a bit slow).</p>
  
  <p>The second part of the question is something that you may not be able to answer without having access to my database but if I run the query for 1, 2 or even 3 weeks (statint.int_num) the query comes back in under 1 minute. as soon as I push it out to 6 weeks (which is what I need) the query takes just over 40 minutes to return the results.</p>
  
  <p>I can't see anywhere in the query where I have created a cartesian but I have stared at it for so long I may be missing it.</p>