Solved

SQL ORACLE: Divisor is equal to zero

Posted on 2010-11-22
4
1,385 Views
Last Modified: 2012-05-10
Experts,

I am getting an error message when I try to run my query.  Here is the error message;

> Script lines: 1-34 -------------------------
 ORA-12801: error signaled in parallel query server P002, instance storm:forge2 (2)
ORA-01476: divisor is equal to zero
 Script line 1, statement line 1, column 0 


I need to find a way to make it show up as 0 or null for the
THEN (FIELD_VALUE/ENDING_PLAN) END)

This is the part that is causing the issue.  See code

Thanks

Using Oracle 10 g
SELECT

FIELD_ID, KEY_INDICATOR,

max(CASE WHEN SHOW_DATE = 'MONDAY   'AND REPORT_ID = 'SO-DYLY-01'  THEN ENDING_PLAN END) MONDAY_E_P_SO_D_1,

max(CASE WHEN SHOW_DATE = 'MONDAY   'AND REPORT_ID = 'SO-DYLY-01'  THEN FIELD_VALUE END) MONDAY_SO_D_1,

max(CASE WHEN SHOW_DATE = 'MONDAY   'AND REPORT_ID = 'SO-DYLY-01'  THEN (FIELD_VALUE/ENDING_PLAN) END) MONDAY_VAR_SO_D_1,



max(CASE WHEN SHOW_DATE = 'TUESDAY  'AND REPORT_ID = 'SO-DYLY-01'  THEN ENDING_PLAN END) TUESDAY_E_P_SO_D_1,

max(CASE WHEN SHOW_DATE = 'TUESDAY  'AND REPORT_ID = 'SO-DYLY-01'  THEN FIELD_VALUE END) TUESDAY_SO_D_1,

max(CASE WHEN SHOW_DATE = 'TUESDAY  'AND REPORT_ID = 'SO-DYLY-01'  THEN (FIELD_VALUE/ENDING_PLAN) END) TUESDAY_VAR_SO_D_1,



max(CASE WHEN SHOW_DATE = 'WEDNESDAY'AND REPORT_ID = 'SO-DYLY-01'  THEN ENDING_PLAN END) WEDNESDAY_E_P_SO_D_1,

max(CASE WHEN SHOW_DATE = 'WEDNESDAY'AND REPORT_ID = 'SO-DYLY-01'  THEN FIELD_VALUE END) WEDNESDAY_SO_D_1,

max(CASE WHEN SHOW_DATE = 'WEDNESDAY'AND REPORT_ID = 'SO-DYLY-01'  THEN (FIELD_VALUE/ENDING_PLAN) END) WEDNESDAY_VAR_SO_D_1,



max(CASE WHEN SHOW_DATE = 'THURSDAY 'AND REPORT_ID = 'SO-DYLY-01'  THEN ENDING_PLAN END) THURSDAY_E_P_SO_D_1,

max(CASE WHEN SHOW_DATE = 'THURSDAY 'AND REPORT_ID = 'SO-DYLY-01'  THEN FIELD_VALUE END) THURSDAY_SO_D_1,

max(CASE WHEN SHOW_DATE = 'THURSDAY 'AND REPORT_ID = 'SO-DYLY-01'  THEN (FIELD_VALUE/ENDING_PLAN) END) THURSDAY_VAR_SO_D_1,



max(CASE WHEN SHOW_DATE = 'FRIDAY   'AND REPORT_ID = 'SO-DYLY-01'  THEN ENDING_PLAN END) FRIDAY_E_P_SO_D_1,

max(CASE WHEN SHOW_DATE = 'FRIDAY   'AND REPORT_ID = 'SO-DYLY-01'  THEN FIELD_VALUE END) FRIDAY_SO_D_1,

max(CASE WHEN SHOW_DATE = 'FRIDAY   'AND REPORT_ID = 'SO-DYLY-01'  THEN (FIELD_VALUE/ENDING_PLAN) END) FRIDAY_VAR_SO_D_1,



max(CASE WHEN SHOW_DATE = 'SATURDAY 'AND REPORT_ID = 'SO-DYLY-01'  THEN ENDING_PLAN END) SATURDAY_E_P_SO_D_1,

max(CASE WHEN SHOW_DATE = 'SATURDAY 'AND REPORT_ID = 'SO-DYLY-01'  THEN FIELD_VALUE END) SATURDAY_SO_D_1,

max(CASE WHEN SHOW_DATE = 'SATURDAY 'AND REPORT_ID = 'SO-DYLY-01'  THEN (FIELD_VALUE/ENDING_PLAN) END) SATURDAY_VAR_SO_D_1,



max(CASE WHEN SHOW_DATE = 'SUNDAY   'AND REPORT_ID = 'SO-DYLY-01'  THEN ENDING_PLAN END) SUNDAY_E_P_SO_D_1,

max(CASE WHEN SHOW_DATE = 'SUNDAY   'AND REPORT_ID = 'SO-DYLY-01'  THEN FIELD_VALUE END) SUNDAY_SO_D_1,

max(CASE WHEN SHOW_DATE = 'SUNDAY   'AND REPORT_ID = 'SO-DYLY-01'  THEN (FIELD_VALUE/ENDING_PLAN) END) SUNDAY_VAR_SO_D_1



FROM NOC_KEY_INDICATOR_VALUES

WHERE REPORT_ID = 'SO-DYLY-01' AND SHOW_DATE IN ( 'SUNDAY   ','SATURDAY ','FRIDAY   ','THURSDAY ','WEDNESDAY','TUESDAY  ','MONDAY   ')

GROUP BY FIELD_ID, KEY_INDICATOR

ORDER BY CAST(REGEXP_REPLACE(Field_ID, '[^[:digit:]]', '') AS INTEGER)

Open in new window

0
Comment
Question by:Maliki Hassani
  • 3
4 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 34189629
THEN decode(ending_plan, 0, 0, FIELD_VALUE/ENDING_PLAN)
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 34189642
you could use case as well

case when ending_plan = 0 then 0 else field_value/ending_plan end
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 34189646
or, if you want null instead of 0


field_value / nullif(ending_plan,0)
0
 

Author Closing Comment

by:Maliki Hassani
ID: 34189667
Thnaks,  I remeber you helping me out with a similar issue but it was a little different syntax.  Thanks!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now