Re: Divide by zero conditions in views and queries.

Instead of:

        sum(commission_paid)/ sum(sales)

maybe you could try:

        decode(sum(sales),null,0,sum(commission_paid)/ sum(sales))

What the DECODE function will do, in the above instance, is give you a value of zero in cases where sum(sales) is null.

Jonathan

On Fri, 12 Jun 1998 15:07:41 GMT, sysdev_at_adpsystems.mb.ca wrote:

>CREATE OR REPLACE VIEW .. AS
>select
>rep, time_period,
>sum(sales) GROSS_SALES,
>sum(commission_paid) COMMISSION,
>sum(commission_paid)/ sum(sales) AVG_COMMISSION_PERCENT
>from ...
>where ...
>group by rep, time_period
>
>
>The problem is, sometimes, the GROSS_SALES
>will have a NULL value. Using this as a
>divisor will give me a divide by zero error.
>
>In the AVG_COMMISSION_PERCENT field,
>I can use the NVL function, to give a small percent:
>
>nvl(sum(sales), 9999999999999999999)
>
>Questions:
>
>1)
>Is there a way, to evaluate the whole expression
>sum(commission_paid)/ sum(sales)
>such that, if sum(sales) is zero or null,
>the result of the whole expression is zero?
Received on Fri Jun 12 1998 - 22:10:05 CDT