Solved

SQL ORACLE: Divisor is equal to zero

Posted on 2010-11-22
4
1,494 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 74

Accepted Solution

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

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 74

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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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
Suggested Courses

634 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