Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Can a JOIN be used here instead of a UNION? Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, September 3, 2014 12:45 AM
 Valued Member Group: General Forum Members Last Login: Monday, March 23, 2015 10:08 PM Points: 62, Visits: 129
 Hi Everyone,I am in the midst of writing a report which shows the 'Total Sales', 'Gross Profit', and 'Gross Profit %' by Sales Person. Traditionally I have used a UNION between two result sets to achieve this task. However I am wondering it if is possible to achieve the same outcome with a JOIN as shown below?SELECT T0.SlpCode , T0.SalesPerson , T0.Branch , (T0.TotalSales - T1.TotalSales) AS 'Total Sales' , ((T0.TotalSales - T1.TotalSales) - (T0.StockValue + T1.StockValue)) AS 'Gross Profit' , CAST(((T0.TotalSales - T1.TotalSales) - (T0.StockValue + T1.StockValue)) / NULLIF((T0.StockValue + T1.StockValue), 0) * 100 AS decimal(15,2)) AS 'Gross Profit %' FROM ( /*** SAP - Invoices by Sales Person ***/ SELECT T1.SlpCode , T2.SlpName AS 'SalesPerson' , T2.U_INE_Branch AS 'Branch' , CAST(SUM(T0.LineTotal) AS decimal(15,2)) AS 'TotalSales' , CAST(SUM(T0.StockValue) AS decimal(15,2)) AS 'StockValue' FROM AU.dbo.INV1 T0 INNER JOIN AU.dbo.OINV T1 ON T1.DocEntry = T0.DocEntry LEFT JOIN AU.dbo.OSLP T2 ON T2.SlpCode = T1.SlpCode WHERE T1.DocDate >= '2014-08-01' AND T1.DocDate <= '2014-09-01' GROUP BY T1.SlpCode, T2.SlpName, T2.U_INE_Branch ) AS T0 FULL JOIN ( /*** SAP - Credits by Sales Person ***/ SELECT T1.SlpCode , T2.SlpName AS 'SalesPerson' , T2.U_INE_Branch AS 'Branch' , ISNULL(CAST(SUM(T0.LineTotal) AS decimal(15,2)), 0) AS 'TotalSales' , ISNULL(CAST(SUM(T0.StockValue) AS decimal(15,2)), 0) AS 'StockValue' FROM AU.dbo.RIN1 T0 INNER JOIN AU.dbo.ORIN T1 ON T1.DocEntry = T0.DocEntry LEFT JOIN AU.dbo.OSLP T2 ON T2.SlpCode = T1.SlpCode WHERE T1.DocDate >= '2014-08-01' AND T1.DocDate <= '2014-09-01' GROUP BY T1.SlpCode, T2.SlpName, T2.U_INE_Branch ) AS T1ON T1.SalesPerson = T0.SalesPersonCurrently I am not getting correct results (which I can do through a UNION), but am instead seeing NULLs scattered throughout, as shown below...(I have blacked out certain details to keep the company I work for anonymous)My question is: are NULLs unavoidable in this scenario simply because of the nature of JOINs? I have tried a 'JOIN' rather than a 'FULL JOIN' but instead of seeing all of the Sales People I only see Sales People who do not have NULLs, which looks nice but is not particularly accurate.Any help and suggestions here will be greatly appreciated.Kind Regards,David
Post #1609885
 Posted Wednesday, September 3, 2014 2:02 AM
 SSCrazy Eights Group: General Forum Members Last Login: 2 days ago @ 10:11 AM Points: 8,587, Visits: 18,753
Post #1609900
 Posted Wednesday, September 3, 2014 6:52 PM
 Valued Member Group: General Forum Members Last Login: Monday, March 23, 2015 10:08 PM Points: 62, Visits: 129
 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 NULLsOn 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
Post #1610325
 Posted Thursday, September 4, 2014 2:00 AM
 SSCrazy Eights Group: General Forum Members Last Login: 2 days ago @ 10:11 AM Points: 8,587, Visits: 18,753
 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 NULLsOn 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,DavidHi DavidYou'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. “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 ModenExploring Recursive CTEs by Example Dwain Camps
Post #1610397
 Posted Thursday, September 4, 2014 5:17 PM
 Valued Member Group: General Forum Members Last Login: Monday, March 23, 2015 10:08 PM Points: 62, Visits: 129
 Thanks Chris, your explanation is very helpful. I now have a much better understanding of NULLIF and ISNULL.
Post #1610706

 Permissions