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.
cindyfillerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

virtuadeptCommented:
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.
0
cindyfillerAuthor Commented:
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
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

cindyfillerAuthor Commented:
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.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
cindyfillerAuthor Commented:
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...
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Under normal circumstances, whenever you're doing a division that may end in zero, and then using it somewhere else, there needs to be a CASE block like this:

SELECT CASE ISNULL(denominator,0)
      WHEN 0 THEN 0
      ELSE numerator / denomator as calculation
FROM SomeTable
    JOIN (Something that creates the denominator value)
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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.
0
cindyfillerAuthor Commented:
Nope...
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
virtuadeptCommented:
In your WHERE clause you have Balance <> 0, but if Balance is NULL, it will not fail that test depending on your SQL server version and settings.

Try making that WHERE ISNULL(Balance,0) <> 0

IF that doesn't do the trick then as suggested above you need to put CASE statements before your division and make sure the denominator is not zero (or null) before dividing.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cindyfillerAuthor Commented:
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!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.