Solved

Divisor is equal to zero

Posted on 2014-11-19
8
185 Views
Last Modified: 2014-11-22
I am getting a "Divisor is equal to zero" error on the below code.  Can someone send back an example to correct.  Thank you!

SELECT
Distinct SN_NM,
(Sum(case when STAT_C IN ('A') AND DEF_PCNT > 1 and DEF_DOL = 0 then 1 else 0 end)+Sum(case when STAT_C IN('A') AND DEF_PCNT < 1 and DEF_DOL > 0 then 1 else 0 end))/Sum(case when STAT_C IN('A') then 1 else 0 end) as PART_RATE
,NVL(ROUND(AVG(CASE when STAT_C IN ('A') AND DEF_PCNT > 1 THEN DEF_PCNT end),2),0) as DEF_RATE

FROM(
 SELECT SSN_N, NVL(DEF_A,0) as DEF_DOL, NVL(DEF_P,0) as DEF_PCNT, STAT_C, DVSN_NM
FROM dbo.V_PART F
  LEFT JOIN(
      SELECT PLAN_ID, SSN_ID,
      CUR__UP_P + SUPP_P As DEF_P,
      CUR__UP_A + INCR_A As DEF_A
      FROM dbo.V_PART_DFRL
   
      ) G ON F.SSN_ID=G.SSN_ID AND  F.PLAN_ID=G.PLAN_ID
     
   
Group by SN_NM
0
Comment
Question by:leezac
8 Comments
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
Seems that You have been warned before about a divide by zero error a few times :
http://www.experts-exchange.com/Database/Oracle/Q_28562393.html#a40443604



If you want a null when the divisor is 0 then try this:
SELECT ns_nm, case when divisor>0 then top_value/divisor end as PART_RATE, def_rate
from (
select 
Distinct SN_NM,
(Sum(case when STAT_C IN ('A') AND DEF_PCNT > 1 and DEF_DOL = 0 then 1 else 0 end)+Sum(case when STAT_C IN('A') AND DEF_PCNT < 1 and DEF_DOL > 0 then 1 else 0 end)) top_value,
Sum(case when STAT_C IN('A') then 1 else 0 end) divisor
,NVL(ROUND(AVG(CASE when STAT_C IN ('A') AND DEF_PCNT > 1 THEN DEF_PCNT end),2),0) as DEF_RATE
FROM(
 SELECT SSN_N, NVL(DEF_A,0) as DEF_DOL, NVL(DEF_P,0) as DEF_PCNT, STAT_C, DVSN_NM
FROM dbo.V_PART F
  LEFT JOIN(
      SELECT PLAN_ID, SSN_ID,
      CUR__UP_P + SUPP_P As DEF_P,
      CUR__UP_A + INCR_A As DEF_A
      FROM dbo.V_PART_DFRL
      ) G ON F.SSN_ID=G.SSN_ID AND  F.PLAN_ID=G.PLAN_ID
Group by SN_NM 
)

Open in new window


If you want something else like a 0 then:
...
case when divisor>0 then top_value/divisor else 0 end as PART_RATE
...

If you want sill something else, what do you want returned?
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
Both slightwv and I noticed and advised you of the "divide by 0" possibility a number of times.

Basically, it looks like you have some rather complex SQL code that you are trying to change, even though you have told us that you are not expert in SQL.

SQL statements with as much complexity as the SQL statements you have posted here are always a red flag to me.  They indicate the at least one of these things is true:
1. The SQL was written by someone who does not know how to write simple SQL statements to get the job done.
2. The application is poorly-designed.
3. The application has poor (or no) data entry validation
4. The database design does not include referential integrity (foreign keys)
5. The application is being used to try to solve problems or answer questions that it was not designed to handle.

Trying to adjust already-complex SQL statements in this kind of environment will be a very challenging task.
0
 

Author Comment

by:leezac
Comment Utility
Slightvw the code gives results but looks like it is returning a percentage which I do not need it to.
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
All I did was attempt to take the formula you had and break it up into two columns so I could check the divisor in the outer query.

Where you had:
select sum(a)/sum(b) from tables;

Open in new window


I made it:
select case when sum_b > 0 then sum_a/sum_b end
from
(
select sum(a) sum_a, sum(b) sum_b from tables
);

Open in new window


If it isn't returning the correct results, take a look at the original formula.

If you want copy/paste code, please provide sample data and expected results so we can set up a test case.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:leezac
Comment Utility
Ok - I know this is not great code, but can use some help with getting it to work for right now.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
I didn't mess with what you had.  I just broke the two SUMs out into two columns then did the same divide you had.

I might have typo'd something when I did it.  Double check it.

If I didn't typo anything and you aren't getting the results you want, then the SQL is wrong.  That I cannot help with since I don't have your data nor do I know your requirements.

If you can provide a simplified test case with data and expected results, we can provide a working example.  You can then take that working example and place your real tables/column in it and it should work.
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
>>Sum(case when STAT_C IN('A') then 1 else 0 end)<<
This will force a 0 denominator whenever STAT_C is not equal to 'A'. If you change "else 0" to "else null" (or just eliminate it since it will default to null), it should return null. Rather than trying to correct what you have, it might be better to supply some sample data and the expected results, as has been suggested, so a better solution could be provided.
0
 

Author Closing Comment

by:leezac
Comment Utility
Thank you
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
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…
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
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

763 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

7 Experts available now in Live!

Get 1:1 Help Now