November 10, 2017 at 7:50 am
I am writing a recursive cte that shows the relationships of containers that are nested inside each other (like a BOM relationship) and displays them in order so that containers nested inside a parent container are listed under the parent container record in the result set.
I cannot see why in my final result set why the column [Full location path] does not show the complete path for the records nested more than 2 levels deep. For example container name 'PAL123' shows 'BAY1a > PAL123' but it should show 'Primary Warehouse > BAY1a > PAL123'.
I managed to get the column [location id hierarchy] to correctly show the IDs of all nested locations.
I do not have a database yet so I have written a block of code below that I am using to practice building my recursive CTE. It uses table variables and you can run it to get exactly what I am seeing.
Please can you tell me how to display the complete container path in the final result?
-- create the container type table
declare @ContainerType table
(
ContainerTypeID int null
,ContainerDescription varchar(50) null
)
insert into @ContainerType (ContainerTypeID,ContainerDescription) select 1, 'big warehouse'
insert into @ContainerType (ContainerTypeID,ContainerDescription) select 2, 'bay'
insert into @ContainerType (ContainerTypeID,ContainerDescription) select 3, 'shelf'
insert into @ContainerType (ContainerTypeID,ContainerDescription) select 4, 'pallet'
insert into @ContainerType (ContainerTypeID,ContainerDescription) select 5, 'small warehouse'
-- create the products table
declare @ProductID table
(
ProductID int null
,ProductDescription varchar(50) null
)
insert into @ProductID (ProductID, ProductDescription) select 1, 'Book A'
insert into @ProductID (ProductID, ProductDescription) select 1, 'Leaflet A'
-- create the containers table which holds the parent/child relationships
declare @Containers table
(
ContainerID int null
,ContainerTypeID int null
,ContainerName varchar(50)
,ParentContainerID int null
,ProductID int null
,Qty int null
)
insert into @Containers (ContainerID, ContainerTypeID, ContainerName, ParentContainerID, ProductID, Qty) select 1, 1, 'Primary Warehouse', null, null, null
insert into @Containers (ContainerID, ContainerTypeID, ContainerName, ParentContainerID, ProductID, Qty) select 2, 2, 'BAY1a', 1, null, null
insert into @Containers (ContainerID, ContainerTypeID, ContainerName, ParentContainerID, ProductID, Qty) select 3, 2, 'BAY2a', 1, null, null
insert into @Containers (ContainerID, ContainerTypeID, ContainerName, ParentContainerID, ProductID, Qty) select 4, 4, 'PAL123', 2, 1, 100
insert into @Containers (ContainerID, ContainerTypeID, ContainerName, ParentContainerID, ProductID, Qty) select 5, 4, 'PAL456', 3, 2, 150
insert into @Containers (ContainerID, ContainerTypeID, ContainerName, ParentContainerID, ProductID, Qty) select 6, 1, 'Secondary Warehouse', null, null, null
insert into @Containers (ContainerID, ContainerTypeID, ContainerName, ParentContainerID, ProductID, Qty) select 7, 2, 'BAY1b', 6, null, null
insert into @Containers (ContainerID, ContainerTypeID, ContainerName, ParentContainerID, ProductID, Qty) select 8, 2, 'BAY2b', 6, null, null
insert into @Containers (ContainerID, ContainerTypeID, ContainerName, ParentContainerID, ProductID, Qty) select 9, 2, 'BAY3b', 6, null, null
insert into @Containers (ContainerID, ContainerTypeID, ContainerName, ParentContainerID, ProductID, Qty) select 10, 4, 'PAL789', 7, 2, 300
insert into @Containers (ContainerID, ContainerTypeID, ContainerName, ParentContainerID, ProductID, Qty) select 11, 4, 'PAL101', 8, 2, 450
insert into @Containers (ContainerID, ContainerTypeID, ContainerName, ParentContainerID, ProductID, Qty) select 12, 4, 'PAL131', 9, 2, 500
select * from @Containers order by ContainerID
-- my recursive cte in which i am trying to order the results in a heirarchy order
;with myResults AS
(
select ContainerID
,ParentContainerID
,ContainerName
, 1 as [level]
,ProductID
,Qty
,CAST(ContainerName as varchar(7200)) as [Path]
,CAST(ContainerID as varchar(101)) as [hierarchyOrder]
from @Containers
where ParentContainerID IS NULL
union all
select [e].ContainerID
,[e].ParentContainerID
,[e].ContainerName
,[h].[level]+1
,[e].ProductID
,[e].Qty
,cast([h].ContainerName + ' > ' + [e].ContainerName as varchar(7200)) as [Path]
,cast([h].[hierarchyOrder] as varchar(50)) + '-' + cast([e].ContainerID as varchar(50)) as [hierarchyOrder]
from @Containers as [e] inner join
myResults as [h] on [e].ParentContainerID = [h].ContainerID
where [h].[level] < 10
)
select ContainerName
,replicate(replicate(char(9),4),[level]-1) + ContainerName as indentedRepresentation
,[Path] as [Full location path]
,ProductID
,Qty
,[hierarchyOrder] [location id heirarchy]
,[level] as [nestingLevel]
from myResults
order by [hierarchyOrder]
November 10, 2017 at 7:54 am
It's because you're using the wrong formula for the path in recursive part of the rCTE. I've commented out the incorrect line and added the correct line.
/* ,cast([h].ContainerName + ' > ' + [e].ContainerName as varchar(7200)) as [Path] */
,cast([h].Path+ ' > ' + [e].ContainerName as varchar(7200)) as [Path]
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 10, 2017 at 8:00 am
drew.allen - Friday, November 10, 2017 7:54 AMIt's because you're using the wrong formula for the path in recursive part of the rCTE. I've commented out the incorrect line and added the correct line.
/* ,cast([h].ContainerName + ' > ' + [e].ContainerName as varchar(7200)) as [Path] */
,cast([h].Path+ ' > ' + [e].ContainerName as varchar(7200)) as [Path]Drew
Awesome - that did it! Thanks for the fast reply too! I've been looking at it for so long and just couldn't see it!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply