Re: HELP: Division by Zero!

Try using a DECODE function in all places where 0 is likely to be a possible divisor - like this ( very pseudo code)

instead of PRICE use DECODE(PRICE,0,1,PRICE) This will divide by 1 ( so no actual change in dividend) when PRICE = 0 and by PRICE when PRICE > 0

hth,

"Abey Joseph" <abeyj_at_netzero.net> 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
>
>

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =----- http://www.newsfeeds.com - The #1 Newsgroup Service in the World! -----== Over 80,000 Newsgroups - 16 Different Servers! =----- Received on Mon Mar 13 2000 - 00:00:00 CST