Home » SQL & PL/SQL » SQL & PL/SQL » Help with Multiple decode functions and divisor is equal to zero.
( ) 1 Vote
| Help with Multiple decode functions and divisor is equal to zero. [message #159980] |
Wed, 22 February 2006 08:57  |
gundr1kr
Messages: 2
Registered: February 2006
|
Junior Member |
|
|
I am having trouble with the decode function and the divsor is equal to zero. This is my query:
select B.LEVEL_4_LABEL, b.level_1_label,
round(sum(decode(A.as_of_date,'01/31/2006',A.BALANCE_MONTH,0))) Cur_Mo_Avg,
round(sum(decode(A.as_of_date,'12/31/2005',A.BALANCE_MONTH,0))) Prev_Mo_Avg,
round(sum(decode(A.as_of_date,'01/31/2006',A.BALANCE_YTD,0))) Current_YTD_Avg,
round(sum(decode(A.as_of_date,'01/31/2005',A.BALANCE_YTD,0))) Prev_Yr_YTD_Avg,
round(sum(decode(A.as_of_date,'01/31/2006',A.BALANCE_YTD,0))) - round(sum(decode(A.as_of_date,'01/31/2005',A.BALANCE_YTD,0))) Incr_Decr,
sum(decode(a.as_of_date,'01/31/2006',A.BALANCE_YTD,0)) - sum(decode(A.as_of_date,'12/31/2005',A.BALANCE_YTD,0)) / sum(decode(A.as_of_date, '12/31/2005', A.BALANCE_YTD, 0)) Pct_Chg
from summary_by_rm_prod a, product_levels b
where a.as_of_date in ('01/31/2005', '12/31/2005','01/31/2006')
and a.officer_nbr = '00504'
and a.officer_sum_type = 'PRM'
and a.level_6_prod = b.level_6_prod
and b.level_1_cd_val in ('AS', 'LI', 'OB')
GROUP BY b.level_1_label, b.level_4_label
order by level_1_label;
Does anyone have any ideas how to fix this?
|
|
|
|
| Re: Help with Multiple decode functions and divisor is equal to zero. [message #159981 is a reply to message #159980] |
Wed, 22 February 2006 09:06  |
Art Metzer
Messages: 2480
Registered: December 2002
|
Senior Member |
|
|
You will have to decide what you'd like to see if the divisor happens to be zero. In my example below, I display NULL if the divisor is zero, using a function called NULLIF. If you'd like something else to display, you should be able to use CASE or DECODE to meet that requirement.SQL> SELECT deptno
2 , SUM(sal) dept_sal
3 , SUM(NVL(comm,0)) dept_comm
4 FROM emp
5 GROUP BY deptno
6 /
DEPTNO DEPT_SAL DEPT_COMM
---------- ---------- ----------
10 8750 0
20 10875 0
30 9400 2200
SQL> SELECT deptno
2 , SUM(sal) / SUM(NVL(comm,0)) some_ratio
3 FROM emp
4 GROUP BY deptno
5 /
, SUM(sal) / SUM(NVL(comm,0)) some_ratio
*
ERROR at line 2:
ORA-01476: divisor is equal to zero
SQL> SET NULL [NULL]
SQL> SELECT deptno
2 , SUM(sal) / NULLIF(SUM(NVL(comm,0)),0) some_ratio
3 FROM emp
4 GROUP BY deptno
5 /
DEPTNO SOME_RATIO
---------- ----------
10 [NULL]
20 [NULL]
30 4.27272727
SQL> |
|
|
|
|
|
|
|
| Re: Help with Multiple decode functions and divisor is equal to zero. [message #159998 is a reply to message #159990] |
Wed, 22 February 2006 10:31 |
Art Metzer
Messages: 2480
Registered: December 2002
|
Senior Member |
|
|
I don't know what OFSA45 is, but do you have any better luck with the following?SQL> SET NULL [NULL]
SQL> SELECT deptno
2 , SUM(sal) / DECODE(SUM(NVL(comm,0))
3 , 0, TO_NUMBER(NULL)
4 , SUM(NVL(comm,0))) some_ratio
5 FROM emp
6 GROUP BY deptno
7 /
DEPTNO SOME_RATIO
---------- ----------
10 [NULL]
20 [NULL]
30 4.27272727
SQL> |
|
|
|
Goto Forum:
Current Time: Tue Jul 29 06:56:50 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
