david.dartnell (9/3/2014)
Hi ChrisM@Work,Thank you for your suggestions. I have now managed to find the line in my query which is returning NULL values.
(T0.TotalSales - T0.StockValue) / NULLIF(T0.StockValue, 0) AS 'Gross Profit %'
I have identified that the NULLIF(T0.StockValue, 0) section of the above line is responsible, even more specifically the T0.StockValue column itself seems to be the source of my challenge. If I modify the above line by replacing T0.StockValue with 0 then I get nothing but NULLs in my result set.
e.g.: NULLIF(0, 0) gives nothing but NULLs
On the other hand if I substitute the number 1 in place of T0.StockValue I get NO NULLs.
e.g.: NULLIF(1, 0) gives a complete set of values, with NO NULLs.
I have used a similar line of SQL in another query and it did not return any NULLs, the line is as follows -
(sum(T0.LineTotal - T0.StockValue) / nullif(sum(T0.StockValue), 0)) * 100 as 'Profit %'
Above I am summing a collection of values so I suppose there is less likely to be a NULL?
At any rate if you have any more suggestions please let me know.
Kind Regards,
David
Hi David
You've gone off on a bit of a tangent here so we'll deal with this first.
Function NULLIF() takes two parameters, p1 and p2. If they are different then p1 is returned, otherwise NULL is returned with data type same as p1.
Removing NULLIF from your expression above yields this:
sum(T0.LineTotal - T0.StockValue) / sum(T0.StockValue)
- which will raise a "divide by zero" error if sum(T0.StockValue) evaluates to 0. Replacing that 0 with NULL quietly yields NULL as the result of the expression. That's what the NULLIF is for - letting the expression complete without raising an error, when sum(T0.StockValue) evaluates to 0.
If you don't like NULL appearing in the result, then change it to 0 using ISNULL(), wrapped around the whole expression like this:
ISNULL((sum(T0.LineTotal - T0.StockValue) / NULLIF(sum(T0.StockValue), 0)) * 100,0)
No NULL, no error, no incorrect result.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden