Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL ORACLE: Divisor is equal to zero

Posted on 2010-11-22
4
Medium Priority
?
1,564 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 2000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
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.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

721 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