HELP: Division by Zero!

From: Abey Joseph <abeyj_at_netzero.net>
Date: 2000/03/13
Message-ID: <8ajo56$d6l$1@nntp3.atl.mindspring.net>#1/1

Folks,

        Attached is the SQL statement from a report. Everything works great, except... We give promotional items away. These items are in the database with a 0 (zero) cost & 0 (zero) price. The SQL statement encountered one of those zero priced items and crapped out. I need to include these zero priced items in the report. Our user wants to see a profit % also. My logic, ((PRICE-COST)/PRICE)*100 works great until it comes across a price of zero (0). I am considering excluding zero priced items on the first run and then running a separate query to add just the zero priced items to the end of the first result. Is there a better way to accomplish what I am trying to do. Also, the price is zero not NULL!

SQL statement...
SELECT cs.prod_num PROD_NUM,
 SUBSTR(pn.prod_name,1,35) PROD_NAME,
 lpad(pd.prod_ndc_num,11,'0') NDC,

 sum(cs.ordd_qty) QTY,
 sum(cs.ordd_price)/100 PRICE,
 sum(cs.ordd_acq_cost)/100 COST,
 sum(cs.ordd_price)/100 - sum(cs.ordd_acq_cost)/100 PROFIT,

ROUND(((sum(cs.ordd_price/100)-sum(cs.ordd_acq_cost/100))/sum(cs.ordd_price/ 100))*100,2) PERCENT
FROM hbs.vw_cost_sales cs, hbs.product_name pn, hbs.product pd

WHERE   cs.prod_num = pn.prod_num
AND     cs.prod_num = pd.prod_num
AND     cs.ord_complete_date BETWEEN '&xStartDate' AND '&xEndDate'

GROUP BY cs.prod_num, pn.prod_name, lpad(pd.prod_ndc_num,11,'0')

Any help appreciated...

Abey Joseph
mailto:abeyj_at_netzero.net Received on Mon Mar 13 2000 - 00:00:00 CST