CTE not working properly

Question

CTE not working properly

skb 44459

SSCarpal Tunnel

Points: 4768
More actions
March 3, 2017 at 9:43 am

#151084

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

Viewing 9 posts - 1 through 8 (of 8 total)

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

Thom A SSC Guru Points: 99126 More actions · Answer 1

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

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

skb 44459 - Friday, March 3, 2017 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

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]

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

skb 44459 SSCarpal Tunnel Points: 4768 More actions · Answer 3

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

Luis Cazares SSC Guru Points: 183698 More actions · Answer 4

What's the problem with that code?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

skb 44459 SSCarpal Tunnel Points: 4768 More actions · Answer 5

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.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 6

skb 44459 - Friday, March 3, 2017 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.

So, your problem is the order of the rows?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

skb 44459 SSCarpal Tunnel Points: 4768 More actions · Answer 7

skb 44459

SSCarpal Tunnel

Points: 4768

March 3, 2017 at 1:12 pm

#1931621

yes

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

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;