March 3, 2017 at 9:43 am
Hierarchy is not getting build properly.
;WITH myCTE2 ([MaterialRowID], [MaterialID], [ParentRowID] )
AS
(
SELECT p1.[MaterialRowID], p1.[MaterialID], p1.[ParentRowID] FROM ki.dbo.NewDrillDown1 P1 WHERE parentrowid IS NULL
UNION ALL
SELECT p2.[MaterialRowID], p2.[MaterialID], p2.[ParentRowID] , FROM ki.dbo.NewDrillDown1 p2
INNER JOIN myCTE2 on myCTe2.materialrowid = p2.materialrowid
)
SELECT * FROM myCTE2
March 3, 2017 at 9:45 am
Could you provide Sample data, along with the expected output please. We have no idea what the problem is from your above post, as we don't have access to your data.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 3, 2017 at 10:03 am
skb 44459 - Friday, March 3, 2017 9:43 AMHierarchy is not getting build properly.;WITH myCTE2 ([MaterialRowID], [MaterialID], [ParentRowID] )
AS
(SELECT p1.[MaterialRowID], p1.[MaterialID], p1.[ParentRowID] FROM ki.dbo.NewDrillDown1 P1 WHERE parentrowid IS NULL
UNION ALL
SELECT p2.[MaterialRowID], p2.[MaterialID], p2.[ParentRowID] , FROM ki.dbo.NewDrillDown1 p2
INNER JOIN myCTE2 on myCTe2.materialrowid = p2.materialrowid
)
SELECT * FROM myCTE2
Your JOIN is wrong.
Change p2.materialrowid for p2.[ParentRowID]
EDIT:
You might want to protect your query from circular references. If all Parents have a lower ID than Materials, then it's easy. Just add AND p2.materialrowid > p2.[ParentRowID]
March 3, 2017 at 12:12 pm
DROP TABLE dbo.MyEmployees
CREATE TABLE dbo.MyEmployees
(
MaterialID nvarchar(300 ) NOT NULL,
MaterialRowID nvarchar(300 ) NOT NULL ,
ParentRowID nvarchar(300 ) ,
CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (MaterialRowID ASC )
)
-- Populate the table with values.
INSERT INTO dbo.MyEmployees VALUES ('490032-G1T1 ( 44.0 (8X8) THR 26.0 X 11.75 JRN W/INSTR )','55',NULL)
INSERT INTO dbo.MyEmployees VALUES ('003257-SRA2 ( .3753/.3751 X .75 DWL PIN PER H4025 )','53','56')
INSERT INTO dbo.MyEmployees VALUES ('2005701 ( 1/2-13 X 1.5 SHCS (SEE NOTES) )','54','56')
INSERT INTO dbo.MyEmployees VALUES ('491006-001 ( 26.0 RETAINING PLATE, SPLIT, END A )','56','55')
INSERT INTO dbo.MyEmployees VALUES ('491005-003 ( 26.0 X 11.75 JRNL PAD ASSY, UPPER )','62','55')
INSERT INTO dbo.MyEmployees VALUES ('386392-002 ( 26 X 11.75 JRNL PAD ASSY, 4X FLD W/INSTR )','63','55')
INSERT INTO dbo.MyEmployees VALUES ('386737-002 ( 26 X 11.75 JRNL PAD ASY, W/WIRE GRV )','64','55')
INSERT INTO dbo.MyEmployees VALUES ('034067-SRA2 ( O-RING MATL .275 DIA VITON A* )','65','55')
INSERT INTO dbo.MyEmployees VALUES ('2005235 ( 1.0003/1.0001 X 1.75 DWL PIN PER H4025 )','66','55')
;WITH
cteReports (MaterialRowID , MaterialID, ParentRowID , MatlLevel, Ctr)
AS
(
SELECT MaterialRowID , MaterialID, ParentRowID, 1, 1
FROM MyEmployees
WHERE ParentRowID IS NULL
UNION ALL
SELECT e. MaterialRowID, e .MaterialID , e. ParentRowID,
r .MatlLevel + 1 , ctr + 1
FROM MyEmployees e
INNER JOIN cteReports r
ON e.ParentRowID = r .MaterialRowID
)
SELECT * FROM cteReports
March 3, 2017 at 12:23 pm
March 3, 2017 at 12:29 pm
This does not build the hierarchy.
I am expecting these records to show (has parent id of 56)
('003257-SRA2 ( .3753/.3751 X .75 DWL PIN PER H4025 )','53','56')
('2005701 ( 1/2-13 X 1.5 SHCS (SEE NOTES) )','54','56')
under
'491006-001 ( 26.0 RETAINING PLATE, SPLIT, END A )','56','55') . this has material id 56
but does not show.
March 3, 2017 at 12:54 pm
skb 44459 - Friday, March 3, 2017 12:29 PMThis does not build the hierarchy.I am expecting these records to show (has parent id of 56)
('003257-SRA2 ( .3753/.3751 X .75 DWL PIN PER H4025 )','53','56')
('2005701 ( 1/2-13 X 1.5 SHCS (SEE NOTES) )','54','56')under
'491006-001 ( 26.0 RETAINING PLATE, SPLIT, END A )','56','55') . this has material id 56
but does not show.
So, your problem is the order of the rows?
March 3, 2017 at 1:12 pm
yes
March 3, 2017 at 1:53 pm
You mean like this?
if OBJECT_ID('dbo.MyEmployees') is not null
DROP TABLE dbo.MyEmployees;
CREATE TABLE dbo.MyEmployees
(
MaterialID nvarchar(300 ) NOT NULL,
MaterialRowID nvarchar(300 ) NOT NULL ,
ParentRowID nvarchar(300 ) ,
CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (MaterialRowID ASC )
);
-- Populate the table with values.
INSERT INTO dbo.MyEmployees VALUES ('490032-G1T1 ( 44.0 (8X8) THR 26.0 X 11.75 JRN W/INSTR )','55',NULL);
INSERT INTO dbo.MyEmployees VALUES ('003257-SRA2 ( .3753/.3751 X .75 DWL PIN PER H4025 )','53','56');
INSERT INTO dbo.MyEmployees VALUES ('2005701 ( 1/2-13 X 1.5 SHCS (SEE NOTES) )','54','56');
INSERT INTO dbo.MyEmployees VALUES ('491006-001 ( 26.0 RETAINING PLATE, SPLIT, END A )','56','55');
INSERT INTO dbo.MyEmployees VALUES ('491005-003 ( 26.0 X 11.75 JRNL PAD ASSY, UPPER )','62','55');
INSERT INTO dbo.MyEmployees VALUES ('386392-002 ( 26 X 11.75 JRNL PAD ASSY, 4X FLD W/INSTR )','63','55');
INSERT INTO dbo.MyEmployees VALUES ('386737-002 ( 26 X 11.75 JRNL PAD ASY, W/WIRE GRV )','64','55');
INSERT INTO dbo.MyEmployees VALUES ('034067-SRA2 ( O-RING MATL .275 DIA VITON A* )','65','55');
INSERT INTO dbo.MyEmployees VALUES ('2005235 ( 1.0003/1.0001 X 1.75 DWL PIN PER H4025 )','66','55');
WITH
cteReports (
MaterialRowID
, MaterialID
, ParentRowID
, MatlLevel
, Ctr
, sortkey)
AS
(
SELECT
MaterialRowID
, MaterialID
, ParentRowID
, 1
, 1
, sortkey = CAST(MaterialRowID as varbinary(max))
FROM
dbo.MyEmployees
WHERE
ParentRowID IS NULL
UNION ALL
SELECT
e.MaterialRowID
, e.MaterialID
, e.ParentRowID
, r.MatlLevel + 1
, ctr + 1
, sortkey = r.sortkey + CAST(e.MaterialRowID as varbinary(max))
FROM
MyEmployees e
INNER JOIN cteReports r
ON
e.ParentRowID = r .MaterialRowID
)
SELECT
MaterialRowID,
MaterialID,
ParentRowID,
MatlLevel,
Ctr
FROM
cteReports
order by
sortkey;
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply