recursive query using cte

Question

Post reply

recursive query using cte

Mike-345690

Grasshopper

Points: 24
More actions
July 5, 2006 at 4:52 am

#169457

I am trying to create a function(s) to peform a recursive query using CTE which is able to traverse up and down the tree to be used with a packing model (box in box etc, on a pallet, in a location). This will enable me to produce a packing list (traversing down) and return a parent (where should i be or what should i be packed in whilst traversing up). The data is stored in a single table self referencing the parent child relationship.

Table: CONTAINER
flds: T_CONTAINER_ID, T_P_CONTAINER_ID
I have tried exprimenting using the code below (not as a function but to test the results), not to much sucess though!
WITH SubCTE AS
(SELECT     T_CONTAINER_ID, T_P_CONTAINER_CODE, 0 AS lvl, CAST('.' + CAST(T_CONTAINER_ID AS    VARCHAR(20)) + '.' AS VARCHAR(MAX))
AS Path
FROM         dbo.CONTAINER
UNION ALL
SELECT     C.T_CONTAINER_ID, C.T_P_CONTAINER_CODE, P.lvl + 1 AS Expr1, CAST(P.Path + CAST(C.T_CONTAINER_ID AS VARCHAR(20)) + '.' AS VARCHAR(MAX)) AS Path
FROM         dbo.CUSP_CONTAINER AS C
INNER JOIN
SubCTE AS P ON C.T_P_CONTAINER_CODE = P.T_CONTAINER_ID)
SELECT     PERCENT T_CONTAINER_ID, REPLICATE(' | ', lvl) + T_P_CONTAINER_CODE AS INDENT, lvl AS Pack_Level, Path
FROM         SubCTE AS SubCTE_1
ORDER BY Path

When executed this correctly displays hierarchical view but at as soon as you specify a starting point such as ;
WITH SubCTE AS
(SELECT     T_CONTAINER_ID, T_P_CONTAINER_CODE, 0 AS lvl, CAST('.' + CAST(T_CONTAINER_ID AS    VARCHAR(20)) + '.' AS VARCHAR(MAX))
AS Path
FROM         dbo.CONTAINER
WHERE T_CONTAINER_ID = xyz
Only the first level of packing are included (box in box but not box in box which is in this box)

I would appreciate any assistance that is out there, thanks in advance...
Mike

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Mike-345690 Grasshopper Points: 24 More actions · Answer 1

Joe,

Thanks, having read some reviews i hear your books are very good..

Kind regards,

Mike

noeld SSC Guru Points: 96590 More actions · Answer 2

He he ... You forgot to include <shameless plug> in your comments joe.

I have read your book and I have read others too and not everything is as cut and dry as you put it on this statement. You "forgot" to mention the cons of the nested set model

Cheers,

* Noel