leezac
asked on
Divisor is equal to zero
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Slightvw the code gives results but looks like it is returning a percentage which I do not need it to.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok - I know this is not great code, but can use some help with getting it to work for right now.
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.
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.
>>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.
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.
ASKER
Thank you
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.