Re: HELP: Division by Zero!

Abey Joseph wrote:
>
> 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

DECODE or GREATEST usually does the trick

select
  x / greatest(y,1)

or

select decode(y,0,0,x/y)

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse