CTE Troubles - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

CTE Troubles

Author

Topic

scootermcfly
Yak Posting Veteran

66 Posts

Posted - 2009-10-20 : 12:06:45

Hi all, I am really close on this and hoping that someone can help, I think it has to do with the join on the last select inside of the CTE, but not positive.

DECLARE Tmp1(
assetnum VARCHAR(20) NOT NULL,
parent VARCHAR(20) NOT NULL)

INSERT INTO Tmp1 VALUES('11400',NULL)
INSERT INTO Tmp1 VALUES('11430','11400')
INSERT INTO Tmp1 VALUES('23972','11430')

-- expected
/*
assetnum ancestor hierarchylevels
------------ ------------ ---------------
11400 11400 0
11430 11430 0
11430 11400 1
23972 23972 0
23972 11430 1
23972 11400 2
*/

-- current cte
WITH AssetAncestors(assetnum, ancestor, hierarchylevels) AS
(
-- create single entries first
SELECT assetnum, assetnum AS ancestor, 0 AS hierarchylevels
FROM Tmp1
WHERE Parent IS NULL
UNION ALL
-- create top level rec for recs with a parent
SELECT assetnum, assetnum AS ancestor, 0 AS hierarchylevels
FROM Tmp1
WHERE Parent IS NOT NULL
UNION ALL
-- get the rest of the data
SELECT T.assetnum, T.parent, hierarchylevels + 1
FROM Tmp1 AS T
JOIN AssetAncestors AS AA ON T.parent = AA.assetnum
)
SELECT assetnum, ancestor, hierarchylevels
FROM AssetAncestors
ORDER BY 1,5;
GO

-- results from the cte
/*
assetnum ancestor hierarchylevels
------------ ------------ ---------------
11400 11400 0
11430 11430 0
11430 11400 1
23972 23972 0
23972 11430 1
23972 11430 2
*/

You can see that the last row in the results from the CTE doesn't have the right ancestor.

Thanks in advance for the help,
Scott

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-10-20 : 16:08:29

Explain the business rules for how to override the sample data.

N 56°04'39.26"
E 12°55'05.63"

scootermcfly
Yak Posting Veteran

66 Posts

Posted - 2009-10-20 : 16:40:38

Hi Peso,

Basically, there is a asset table in the IBM Maximo system, and each asset can have a parent or not, hence the NULL in the parent column in the first row.

Now, there is also a assetancestor table which takes each assetnum and creates a entry starting at 0 and then gets each one of the parents and parent's parent record. This builds the list of all the parents that are for that asset record and this is used by the application.

I have a stored procedure that will re-create the assetancestor table already, but was trying to build a CTE that would do it. I came close, but as you can see in the last record, the ancestor isn't right.

Thanks for any help you can provide,
Scott

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-10-20 : 17:50:57

[code]DECLARE @Sample TABLE
(
Asset VARCHAR(20),
Parent VARCHAR(20)
)

INSERT @Sample
SELECT '11400', NULL UNION ALL
SELECT '11430', '11400' UNION ALL
SELECT '23972', '11430'

;WITH Yak(Asset, Parent, Levels)
AS (
SELECT Asset,
Asset,
0
FROM @Sample

UNION ALL

SELECT s.Asset,
s.Parent,
y.Levels + 1
FROM @Sample AS s
INNER JOIN Yak AS y ON y.Parent = s.Asset
WHERE s.Parent IS NOT NULL
)

SELECT Asset,
Parent,
Levels
FROM Yak[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources