Divide by zero conditions in views and queries.

[Quoted] Hi all,

We are using Oracle 7.2.3.

I have to create a view that does a group by, and also creates a number of percentages.

ie.
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

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?

2)
Is NVL the only method available?

3)
If NVL is the only method available, is there a way to indicate a MAXIMUM_INTEGER in simple and short manner in the view definition ??

[Quoted] Our ISP is really bad with the newsgroups. Emails to
sysdev_at_adpsystems.mb.ca
are greatly appreciated.

Regards,

Rodger

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Fri Jun 12 1998 - 00:00:00 CEST