September 7, 2022 at 4:15 pm
I have the following working example:
DROP TABLE IF EXISTS consignment_levels;
GO
CREATE TABLE consignment_levels(
consignment_id INT NOT NULL,
parent_id CHAR(10) NULL,
level_id CHAR(10) NOT NULL,
level_type CHAR(10) NOT NULL,--case, master_case, pallet
);
CREATE UNIQUE INDEX consignment_levels_U01 ON consignment_levels (consignment_id,parent_id,level_id);
GO
DECLARE @v_consignment_id INT = 1000;
DECLARE @c_pallet CHAR(6) = 'PALLET',
@c_master_case CHAR(6) = 'MASTER',
@c_case CHAR(4) = 'CASE',
@c_box CHAR(3) = 'BOX';
INSERT INTO consignment_levels(consignment_id,parent_id,level_id, level_type)
VALUES (@v_consignment_id,NULL,'P1',@c_pallet),
(@v_consignment_id,NULL,'P2',@c_pallet),
(@v_consignment_id,NULL,'P3',@c_pallet),
(@v_consignment_id,NULL,'P4',@c_pallet),
(@v_consignment_id,NULL,'P5',@c_pallet),
(@v_consignment_id,NULL,'P6',@c_pallet),
(@v_consignment_id,'P2','M1',@c_master_case),
(@v_consignment_id,'P2','M2',@c_master_case),
(@v_consignment_id,'P2','M3',@c_master_case),
(@v_consignment_id,'P2','M4',@c_master_case),
(@v_consignment_id,'P3','M11',@c_master_case),
(@v_consignment_id,'P3','M12',@c_master_case),
(@v_consignment_id,'P3','M13',@c_master_case),
(@v_consignment_id,'P3','M14',@c_master_case),
(@v_consignment_id,'M3','C1',@c_case),
(@v_consignment_id,'M3','C2',@c_case),
(@v_consignment_id,'M3','C3',@c_case),
(@v_consignment_id,'M3','C4',@c_case),
(@v_consignment_id,'M3','C5',@c_case),
(@v_consignment_id,'M3','C6',@c_case),
(@v_consignment_id,'M3','C7',@c_case),
(@v_consignment_id,'M3','C8',@c_case),
(@v_consignment_id,'M3','C9',@c_case),
(@v_consignment_id,'M2','C10',@c_case),
(@v_consignment_id,'M4','C30',@c_case),
(@v_consignment_id,'M4','C20',@c_case),
(@v_consignment_id,'C1','B1',@c_box),
(@v_consignment_id,'C1','B2',@c_box),
(@v_consignment_id,'C1','B3',@c_box),
(@v_consignment_id,'C1','B4',@c_box),
(2,NULL,'P20',@c_pallet),
(2,NULL,'P30',@c_pallet),
(2,NULL,'P40',@c_pallet);
WITH consignment_tree (consignment_id,parent_id,level_id, level_type,tree_level)
AS
(
SELECT cc.consignment_id,
cc.parent_id,
cc.level_id,
cc.level_type,
0
FROM consignment_levels cc
WHERE cc.parent_id IS NULL
UNION ALL
SELECT cc.consignment_id,
cc.parent_id,
cc.level_id,
cc.level_type,
tree_level+1
FROM consignment_levels cc
INNER JOIN consignment_tree ct
ON ct.level_id = cc.parent_id
AND ct.consignment_id = cc.consignment_id
)
SELECT * FROM consignment_tree ct
WHERE ct.consignment_id = @v_consignment_id
ORDER BY ct.tree_level,
ct.parent_id,
ct.level_id
OPTION (MAXRECURSION 5);
The same type of cte is wrapped in a view and works as expected.
Now I have a requirement to drill down and to show the structure from a specific record down. Based on my example above, if the user requests to see the structure from Master M3 onwards the system should return the following records:
1000 M3 C1 CASE
1000 M3 C2 CASE
1000 M3 C3 CASE
1000 M3 C4 CASE
1000 M3 C5 CASE
1000 M3 C6 CASE
1000 M3 C7 CASE
1000 M3 C8 CASE
1000 M3 C9 CASE
1000 C1 B1 BOX
1000 C1 B2 BOX
1000 C1 B3 BOX
1000 C1 B4 BOX
All the cases, which belong to M3 master and all the boxes which belong to those cases.
Any suggestion will be extremely appreciated as usual.
Thank you.
September 7, 2022 at 4:59 pm
Does filtering the first part of the cte return the data you need?
WHERE level_id = 'M3'
with
WHERE tree_level > 0
If so you could turn it into a table function which will work as a parameterised view.
September 7, 2022 at 7:32 pm
Yes, function is a bit of an issue here - has to be a model in Java at the end, so either view or table.
September 7, 2022 at 10:22 pm
Is the hierarchy limited to those four levels? If so you could always include a Pallet, Master and Case column in the view. Assuming the level_ids don't cross level type, your query view could use an OR to avoid dynamically choosing a column to filter.
DROP VIEW IF EXISTS dbo.v_consignment_tree
GO
CREATE VIEW dbo.v_consignment_tree AS
WITH consignment_tree (consignment_id,parent_id,level_id, level_type,tree_level, c_pallet, c_master_case, c_case)
AS
(
SELECT cc.consignment_id,
cc.parent_id,
cc.level_id,
cc.level_type,
0 ,
cc.level_id AS c_pallet,
CONVERT(VARCHAR(10),null) as c_master_case,
CONVERT(VARCHAR(10),null) as c_case
FROM consignment_levels cc
WHERE cc.parent_id IS NULL
UNION ALL
SELECT cc.consignment_id,
cc.parent_id,
cc.level_id,
cc.level_type,
tree_level+1,
ct.c_pallet,
iif(cc.level_type = 'MASTER', cc.level_id, ct.c_master_case) as c_master_case,
iif(cc.leveL_type = 'CASE', cc.level_id, ct.c_case) as c_case
FROM consignment_levels cc
INNER JOIN consignment_tree ct
ON ct.level_id = cc.parent_id
AND ct.consignment_id = cc.consignment_id
)
SELECT * FROM consignment_tree ct
GO
DECLARE @Consignment_id INT = 1000,
@Level_id VARCHAR(10) = 'M3'
SELECT *
FROM dbo.v_consignment_tree
WHERE consignment_id = @Consignment_id
AND ( c_pallet = @Level_id
OR c_master_case = @Level_id
OR c_case = @Level_id
)
Adding a hierarchyid might be an option. I had a go at it with your data. I think it works, but I manually created the node column so you'd have to come up with a way to do that. If each item is assigned a number, you could probably use a recursive cte to generate the node strings. Might be more hassle than it's worth though.
DROP TABLE IF EXISTS consignment_levels;
GO
CREATE TABLE consignment_levels
(
Consignment_ID INT NOT NULL,
Consignment_Node hierarchyid NOT NULL,
Consignment_Code VARCHAR(10) NOT NULL,
Consignment_Level VARCHAR(10) NOT NULL,
);
DECLARE @v_consignment_id INT = 1000;
DECLARE @c_pallet CHAR(6) = 'PALLET',
@c_master_case CHAR(6) = 'MASTER',
@c_case CHAR(4) = 'CASE',
@c_box CHAR(3) = 'BOX';
INSERT INTO consignment_levels(Consignment_ID,Consignment_Node,Consignment_Code, Consignment_Level)
VALUES (@v_consignment_id,'/1/','P1',@c_pallet),
(@v_consignment_id,'/2/','P2',@c_pallet),
(@v_consignment_id,'/3/','P3',@c_pallet),
(@v_consignment_id,'/4/','P4',@c_pallet),
(@v_consignment_id,'/5/','P5',@c_pallet),
(@v_consignment_id,'/6/','P6',@c_pallet),
(@v_consignment_id,'/2/1/','M1',@c_master_case),
(@v_consignment_id,'/2/2/','M2',@c_master_case),
(@v_consignment_id,'/2/3/','M3',@c_master_case),
(@v_consignment_id,'/2/4/','M4',@c_master_case),
(@v_consignment_id,'/3/5/','M11',@c_master_case),
(@v_consignment_id,'/3/6/','M12',@c_master_case),
(@v_consignment_id,'/3/7/','M13',@c_master_case),
(@v_consignment_id,'/3/8/','M14',@c_master_case),
(@v_consignment_id,'/2/3/1/','C1',@c_case),
(@v_consignment_id,'/2/3/2/','C2',@c_case),
(@v_consignment_id,'/2/3/3/','C3',@c_case),
(@v_consignment_id,'/2/3/4/','C4',@c_case),
(@v_consignment_id,'/2/3/5/','C5',@c_case),
(@v_consignment_id,'/2/3/6/','C6',@c_case),
(@v_consignment_id,'/2/3/7/','C7',@c_case),
(@v_consignment_id,'/2/3/8/','C8',@c_case),
(@v_consignment_id,'/2/3/9/','C9',@c_case),
(@v_consignment_id,'/2/2/1/','C10',@c_case),
(@v_consignment_id,'/2/4/1/','C30',@c_case),
(@v_consignment_id,'/2/4/2/','C20',@c_case),
(@v_consignment_id,'/2/3/1/1/','B1',@c_box),
(@v_consignment_id,'/2/3/1/2/','B2',@c_box),
(@v_consignment_id,'/2/3/1/3/','B3',@c_box),
(@v_consignment_id,'/2/3/1/4/','B4',@c_box),
(2,'/20/','P20',@c_pallet),
(2,'/30/','P30',@c_pallet),
(2,'/40/','P40',@c_pallet),
(@v_consignment_id,'/20/15/','M15',@c_master_case),
(@v_consignment_id,'/30/16/','M16',@c_master_case),
(@v_consignment_id,'/30/17/','M17',@c_master_case),
(@v_consignment_id,'/20/15/100/','C100',@c_case),
(@v_consignment_id,'/20/15/200/','C200',@c_case),
(@v_consignment_id,'/20/15/201/','C201',@c_case)
DECLARE @Parent VARCHAR(100) = 'M3', --'P20'
@ParentNode HIERARCHYID
SET @ParentNode = (SELECT Consignment_Node FROM consignment_levels WHERE Consignment_Code = @Parent)
SELECT Consignment_Code,
Consignment_Level,
Consignment_Node.ToString() AS NodeText,
Consignment_Node.GetLevel() AS NodeLevel,
Consignment_Node
FROM consignment_levels
WHERE Consignment_Node.IsDescendantOf(@ParentNode) = 1
September 8, 2022 at 11:32 am
I was afraid you will be using the nodes.
September 8, 2022 at 2:19 pm
I was afraid you will be using the nodes.
What makes it tricky is you need to show the children of an entity at any level
Here's a potential alternative. If you concat the levels to create a pseudo-hierarchyid you can use patindex to identify rows there the variable level_id is earlier in the string than the level_id of the row. In the case of M3, P2 is earlier in the string so it is excluded.
GO
CREATE OR ALTER VIEW dbo.vconsignment_tree
AS
WITH consignment_tree (consignment_id,parent_id,level_id, level_type,tree_level, level_id_string)
AS
( SELECT cc.consignment_id,cc.parent_id,cc.level_id,
cc.level_type,0 ,
CONVERT(VARCHAR(100),CONCAT('/', LTRIM(RTRIM(cc.level_id)), '/')) AS level_id_string
FROM consignment_levels cc
WHERE cc.parent_id IS NULL
UNION ALL
SELECT cc.consignment_id,
cc.parent_id,cc.level_id,cc.level_type,tree_level+1,
CONVERT(VARCHAR(100),CONCAT(ct.level_id_string,LTRIM(RTRIM(cc.level_id)), '/')) as level_id_string
FROM consignment_levels cc
INNER JOIN consignment_tree ct ON ct.level_id = cc.parent_id
AND ct.consignment_id = cc.consignment_id )
SELECT * FROM consignment_tree ct
GO
DECLARE @consignment_id int = 1000,
@level_id varchar(20) = 'M3'
DECLARE @level_id_string varchar(20) = concat('%', @level_id, '%')
SELECT *
FROM dbo.vconsignment_tree AS a
WHERE consignment_id = @consignment_id
AND patindex(@level_id_string, level_id_string) > 0
AND patindex(@level_id_string, level_id_string) <= patindex (concat('%', rtrim(a.level_id), '%') , level_id_string)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply