asked on

Bizarre divide by zero message when I'm not dividing

I'm trying to figure out why a certain SSRS report is creating bizarre lines. I've taken the code over to the management studio and am trying to run it there. I keep getting a divide by zero error encountered but the line it references (and highlights) has no division in it. This is the line it is saying there is an issue on

SELECT 1 AS Grouper, CR.*, TotalYrBalance, TotalBalance, TotalProject, Average,

I've moved the line up and down and it always errors out on this line. I don't now how it can be. Because I can't get past this error, I can't figure out what is wrong with my SSRS report. And when I try and run the code from the SSRS side of things I get a bizarre CLR type does not exist or you do not have permission to access it.

Note - the report does run fine and the majority of the lines are fine.... it's just when I have a certain kind of record that it randomly puts odd dollars in the report. I'm trying to trouble shoot this random problem.

virtuadept

Can you post the entire SELECT statement. The divide could be in the WHERE not just in the column specifications. Also designate which sources of the select are views and which are tables. A view could also be causing a divide by zero if it is a complex view and doesn't get hit much for certain types of data.

IF you have examples of the records you know it dies on that would also help.

cindyfiller

ASKER

Here is the entire code - lots of division in there. This code is used in 2 different reports both having to do with unitized investments. The summary report works fine and prints fine... I then have to summary report and create a journal entry for 6 different accounts for each line of the summary report. 90% of the lines are fine on this second report as well, but we have about 25 projects that have a matching component. When a fund has this match, it generates a 2nd line of totals that doesn't appear to relate to anything.

SELECT 1 AS Grouper, CR.*, TotalYrBalance, TotalBalance, TotalProject, Average,
CASE WHEN FeePerc IS NULL THEN 1.9 ELSE cast(FeePerc AS decimal(5, 2)) END AS FeePerc, CASE WHEN FeePerc IS NULL
THEN ((1.9 / 100 * Balance) / 4) ELSE (((cast(FeePerc AS decimal(5, 2)) / 100) * Balance) / 4) END AS FndMgmtFee,
(CAST(Balance AS Decimal(19, 5)) / TotalBalance * 522334.32) AS IntDivP, (CAST(Balance AS Decimal(19, 5)) / TotalBalance * 6072547.53) AS GLP,
(CAST(Balance AS Decimal(19, 5)) / TotalBalance * 184277.52) AS IMFP, (CAST(Balance AS Decimal(19, 5))
/ TotalBalance * (522334.32 + 6072547.53 + (184277.52 * (- 1)))) AS TotDist, CASE WHEN FeePerc IS NULL THEN (CAST(Balance AS Decimal(19, 5))
/ TotalBalance * 522334.32) + (CAST(Balance AS Decimal(19, 5)) / TotalBalance * 6072547.53) - (CAST(Balance AS Decimal(19, 5))
/ TotalBalance * 184277.52) - ((1.9 / 100 * Balance) / 4) ELSE (CAST(Balance AS Decimal(19, 5)) / TotalBalance * 522334.32)
+ (CAST(Balance AS Decimal(19, 5)) / TotalBalance * 6072547.53) - (CAST(Balance AS Decimal(19, 5)) / TotalBalance * 184277.52)
- ((cast(FeePerc AS decimal(5, 2)) / 100 * Balance) / 4) END AS Total, CASE WHEN FeePerc IS NULL THEN ((CAST(Balance0005 AS Decimal(19, 5))
/ TotalBalance * 522334.32) + (CAST(Balance0005 AS Decimal(19, 5)) / TotalBalance * 6072547.53) - (CAST(Balance0005 AS Decimal(19, 5))
/ TotalBalance * 184277.52) - ((1.9 / 100 * Balance0005) / 4)) ELSE ((CAST(Balance0005 AS Decimal(19, 5)) / TotalBalance * 522334.32)
+ (CAST(Balance0005 AS Decimal(19, 5)) / TotalBalance * 6072547.53) - (CAST(Balance0005 AS Decimal(19, 5)) / TotalBalance * 184277.52)
- ((cast(FeePerc AS decimal(5, 2)) / 100 * Balance0005) / 4)) END AS Total05, CASE WHEN FeePerc IS NULL THEN ((CAST(Balance0607 AS Decimal(19,
5)) / TotalBalance * 522334.32) + (CAST(Balance0607 AS Decimal(19, 5)) / TotalBalance * 6072547.53) - (CAST(Balance0607 AS Decimal(19, 5))
/ TotalBalance * 184277.52) - ((1.9 / 100 * Balance0607) / 4)) ELSE ((CAST(Balance0607 AS Decimal(19, 5)) / TotalBalance * 522334.32)
+ (CAST(Balance0607 AS Decimal(19, 5)) / TotalBalance * 6072547.53) - (CAST(Balance0607 AS Decimal(19, 5)) / TotalBalance * 184277.52)
- ((cast(FeePerc AS decimal(5, 2)) / 100 * Balance0607) / 4)) END AS Total06, (Balance0005 / Balance) AS [Perc051], CAST(Balance0005 AS Decimal(19,
5)) / Balance AS [Perc05], (Balance0607 / TotalProject) AS [Perc06]
FROM ProjectBalances_CR_Report AS CR INNER JOIN
(SELECT SUM(Balance) AS TotalProject,
(SELECT SUM(Balance) AS Expr1
FROM ProjectBalances_CR_Report
WHERE (Balance <> 0) AND (quarterEnd = '9/30/13') AND (EndowedProject IS NOT NULL)) AS TotalBalance,
(SELECT SUM(Balance) AS Expr1
FROM ProjectBalances_CR_Report AS ProjectBalances_CR_Report_2
WHERE (Balance <> 0) AND (quarterEnd > '7/1/13') AND (quarterEnd <= '9/30/13') AND (EndowedProject IS NOT NULL))
AS TotalYrBalance, SUM(Balance) /
(SELECT SUM(Balance) AS Expr1
FROM ProjectBalances_CR_Report AS ProjectBalances_CR_Report_1
WHERE (Balance <> 0) AND (quarterEnd = '9/30/13') AND (EndowedProject IS NOT NULL)) AS WeightAvg, SUM(Balance)
/ 4 AS Average, COUNT(Quarter) AS QtrCnt, ProjectID, SUM(Balance0005) / SUM(Balance) AS Perc05, SUM(Balance0607) / SUM(Balance)
AS Perc06
FROM ProjectBalances_CR_Report AS CR
WHERE (quarterEnd > '7/1/13') AND (quarterEnd <= '9/30/13')
GROUP BY ProjectID) AS CR1 ON CR.ProjectID = CR1.ProjectID LEFT OUTER JOIN
(SELECT ProjectDimID, AttributeCategory, AttributeDescription AS FeePerc
FROM DIM_ProjectAttribute
WHERE (AttributeCategory = 'UNDF Investment Mgmt Fee')) AS PA1 ON CR.ProjectDimID = PA1.ProjectDimID
WHERE (CR.EndowedProject IS NOT NULL) AND (CR.BalanceDate IS NULL) OR
(CR.EndowedProject IS NOT NULL) AND (CR.quarterEnd = '9/30/13')
ORDER BY CR.EndowedProject, CR.Quarter

Jim Horn

Since my TSQL-mind-reading skills are terrible, here are some wild guesses

The query hits a table or view that contains a calculated column, where one of the values is zero

Somewhere in here is a functional call that returns a divide by zero

The error is happening somewhere other than the select fragment in the question

Your report somewhere has an expression that is dividing by zero, and if you run it in the designer an error message will be returned that says where

A co-worker is playing a really excellent practical joke on you.

cindyfiller

ASKER

Funny - no co-worker around that would have a clue what I'm doing.

I did take the code to management studio so I could find the line with the error - that's the one that is telling me its on the first line, which it isn't. I'm doing a lot of calculations throughout the code.

Jim Horn

Good Lord that is ugly.

>SELECT SUM(Balance) AS Expr1 ... )) AS TotalBalance,
Show me where in your T-SQL you are handling what to do if Sum(Balance) = 0, as I see a gazillion {some expression] / TotalBalance in the SELECT.
I'm not seeing it.

cindyfiller

ASKER

I do have a where clause that only selects records where the balance <> 0.

BTW this isn't my original code - it was done by a vendor...

SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jim Horn

>BTW this isn't my original code - it was done by a vendor...
Please tell me your company is charging this vendor to rewrite this pile of goo.

cindyfiller

ASKER

Nope...

Jim Horn

Just so you know, it's probably unrealistic to expect experts here to take over the entire T-SQL that you've posted and provide a fix, especially since we're not connected to your data source, but by using the CASE example three comments up you'll be able to resolve this.

ASKER CERTIFIED SOLUTION

virtuadept

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cindyfiller

ASKER

I found my original problem in the report side, so am not as hyped to get the sql side working. I think both the suggestions are great and will award points on them both and close the case. Thanks!