divided by zero error encountered

Question

Can someone help me getting this error out of this query. I can figure it out.

Thanks

SELECT p21_sales_history_report_view.invoice_date AS 'Invoice Date'
, p21_sales_history_report_view.customer_id AS 'Cust. Id'
, p21_sales_history_report_view.customer_name AS 'Cust. Name'
, inv_mast.default_purchase_disc_group AS 'Division'
, p21_sales_history_report_view.product_group_desc AS 'Product Group'
, inv_mast.class_id1 AS 'Brand'
, p21_sales_history_report_view.invoice_no AS 'Inv. No.'
, p21_sales_history_report_view.item_id AS 'Item ID'
, p21_sales_history_report_view.item_desc AS 'Item Description'
, p21_sales_history_report_view.qty_shipped AS 'Qty'
, p21_sales_history_report_view.sales_price AS 'Net Sales'
, p21_sales_history_report_view.cogs_amount AS 'Total Cost'
, p21_sales_history_report_view.total_sales AS 'Invoice Amount'
, p21_sales_history_report_view.other_charge_amount AS 'Discount'
, p21_sales_history_report_view.sales_location_id
, (p21_sales_history_report_view.sales_price-p21_sales_history_report_view.cogs_amount)/p21_sales_history_report_view.sales_price AS 'GM%'  
FROM 
    P21.dbo.inv_mast inv_mast, 
    P21.dbo.p21_sales_history_report_view p21_sales_history_report_view  
WHERE inv_mast.item_id = p21_sales_history_report_view.item_id
    AND ((p21_sales_history_report_view.customer_id=?) AND (p21_sales_history_report_view.year_for_period=?) 
    AND (p21_sales_history_report_view.other_charge_item='N') 
    AND (inv_mast.default_purchase_disc_group<>'PARTS'))  
ORDER BY p21_sales_history_report_view.product_group_desc

Result

Customer ID Year
3   3
Invoice Date    Cust. Id    Cust. Name  Division    Product Group   Brand   Inv. No.    Item ID Item Description

`p21_sales_history_report_view.sales_price` is coming up as zero. what's the question? — DLeh, Jan 20 '15 at 14:26
http://stackoverflow.com/questions/861778/how-to-avoid-the-divide-by-zero-error-in-sql?rq=1 — Matteo Tassinari, Jan 20 '15 at 14:27
What's the expected result when p21_sales_history_report_view.sales_price = 0? — jarlh, Jan 20 '15 at 14:42

score 0 · Answer 1 · answered Jan 20 '15 at 15:42

Use a case construct to check for zero:

, case when p21_sales_history_report_view.sales_price = 0 then 
    null 
  else 
    (p21_sales_history_report_view.sales_price-p21_sales_history_report_view.cogs_amount)/p21_sales_history_report_view.sales_price 
  end AS 'GM%'

BTW: Your query would be much better readable, if you used shorter alias names for the tables. As to column aslias names: ' is non-standard. " is the standard for alias names.

divided by zero error encountered

1 Answers1