July 24, 2018 at 8:00 am
Hi Team,
Firstly a Happy Tuesday to Everyone!!
I just want to understand th code.I basically see a self join in the query but my question is Why do we need "WHERE p.ProductName > c.product_name" statement
Any explanation on this would be a great help to me.
WITH CTE ( CategoryId, product_list, product_name, length )
AS (
SELECT
CategoryId
, CAST( '' AS VARCHAR(8000) ), CAST( '' AS VARCHAR(8000) ), 0
FROM
Northwind..Products
GROUP BY
CategoryId
UNION ALL
SELECT
p.CategoryId
, CAST( product_list + CASE WHEN length = 0 THEN '' ELSE ', ' END + ProductName AS VARCHAR(8000) ),
CAST( ProductName AS VARCHAR(8000))
, length + 1
FROM CTE c
INNER JOIN Northwind..Products p ON c.CategoryId = p.CategoryId
WHERE p.ProductName > c.product_name
)
Thanks in Advance!!
July 24, 2018 at 8:13 am
It's a recursive CTE (note how the second part of the UNION ALL refers to the CTE being defined). It looks like it's aggregating a bunch of product names into a comma separated list. The WHERE clause is slowly reducing the set of valid responses so the CTE will complete before it hits the maximum recursion limit (which defaults to 100 IIRC).
That said, I'm not 100% sure the code actually does what it's probably intending to. Without an ORDER BY in the second half of the CTE there is nothing to guarantee you don't accidentally skip over products, nor is there anything to cope with a product being named the same as another (which may or may not be an issue).
July 24, 2018 at 8:16 am
The code sample is a recursive CTE (rCTE) and the WHERE clause is there to prevent infinite recursion, but this is a bad example to be following, because an rCTE is a horribly expensive way to accomplish a concatenated product list.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 24, 2018 at 9:53 am
Noting that this is a Northwind database, I'm assuming that it's an example to learn about recursive CTEs. As mentioned, they can be expensive and easily outperformed by other methods. One of those, is the set-based loop that I explain in here: http://www.sqlservercentral.com/articles/set-based+loop/127670/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply