Solved

SQL ORACLE: Divisor is equal to zero

Posted on 2010-11-22
4
1,459 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to recover a database from a user managed backup
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

734 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