Creating a recursive CTE with no rootrecord

Question

My Apologies for the appalling Title, I was trying to be descriptive but not sure I got to the point. Hopefully the below will explain it

I begin with a table that has the following information

Party Id    Party Name    Party Code   Parent Id
1           Acme 1        ACME1        1
2           Acme 2        ACME2        1
3           Acme 3        ACME3        3
4           Acme 4        ACME4        4
5           Acme 5        ACME5        4
6           Acme 6        ACME6        6

As you can see this isn't perfect for a recursive CTE because rather than having a NULL where there isn't a parent record it is instead parented to itself (see rows 1,3 and 6). Some however are parented normally.

I have therefore tried to amend this table in a CTE then refer to the output of that CTE as part of my recursive query... This doesn't appear to be running very well (no errors yet) so I wonder if I have managed to create an infinite loop or some other error that just slows the query to a crawl rather than killing it

My Code is below... please pick it apart!

--This is my attempt to 'clean' the data and set records parented to themselves as the 'anchor' 
--record
WITH Parties
AS
(Select CASE
    WHEN Cur_Parent_Id = Party_Id THEN NULL
    ELSE Cur_Parent_Id
END AS Act_Parent_Id
, Party_Id
, CUR_PARTY_CODE
, CUR_PARTY_NAME
FROM EDW..TBDIMD_PARTIES
WHERE CUR_FLG = 1),

--In this CTE I referred to my 'clean' records from above and then traverse through them
--looking at the actual parent record identified
linkedParties
AS
(
Select Act_Parent_Id, Party_Id, CUR_PARTY_CODE, CUR_PARTY_NAME, 0 AS LEVEL
FROM Parties
WHERE Act_Parent_Id IS NULL

UNION ALL

Select p.Act_Parent_Id, p.Party_Id, p.CUR_PARTY_CODE, p.CUR_PARTY_NAME, Level + 1
FROM Parties p
inner join
linkedParties t on p.Act_Parent_Id = t.Party_Id
)

Select *
FROM linkedParties
Order By Level

From the data I supplied earlier the results I would expect are;

Party Id    Party Name    Party Code   Parent Id    Level
1           Acme 1        ACME1        1            0
3           Acme 3        ACME3        3            0
4           Acme 4        ACME4        4            0
6           Acme 6        ACME6        6            0
2           Acme 2        ACME2        1            1
5           Acme 5        ACME5        4            1

If everything seems to be OK then I'll assume its just a processing issue and start investigating that but I am not entirely comfortable with CTE's so wish to make sure the error is not mine before looking elsewhere.

Many Thanks

score 1 · Accepted Answer · answered Oct 24 '14 at 10:08

1

I think that you made it more complicated than it needs to be :).

drop table #temp
GO
select 
    *
into #temp
from (
    select '1','Acme 1','ACME1','1' union all
    select '2','Acme 2','ACME2','1' union all
    select '3','Acme 3','ACME3','3' union all
    select '4','Acme 4','ACME4','4' union all
    select '5','Acme 5','ACME5','4' union all
    select '6','Acme 6','ACME6','6'
) x ([Party Id],[Party Name],[Party Code],[Parent Id])
GO

;with cte as (
    select
        *, 
        [Level] = 0
    from #temp
    where 1=1
        and [Party Id]=[Parent Id] --assuming these are root records

    union all 

    select
        t.*,
        [Level] = c.[Level]+1
    from #temp t
    join cte c
        on t.[Parent Id]=c.[Party Id]
    where 1=1
        and t.[Party Id]<>t.[Parent Id]  --prevent matching root records with themselves creating infinite recursion
)
select
    *
from cte

(* should ofcourse be replaced with actual column names)

answered Oct 24 '14 at 10:08

AdamL

12,421
5
50
74

I did, and partly as a result of laziness because I'd made an error in my code with a temp table and assumed it was a compatibility thing between CTEs and temp tables... I later realised I had = NULL rather than IS NULL. I've amended back to how yours is above (not exactly but close enough) and it's running exactly as required, but would the CTE version work too (theoretically)? Are there any performance considerations between using the temp table and the multiple CTE? – Neil Rutland Oct 24 '14 at 10:20
@NeilRutland I only used temp table to provide working example :). Replace `#temp` with your actual table name. – AdamL Oct 24 '14 at 10:58
I don't have an actual table. The actual table has the issue with records being parented to itself so it needs a temp table to simulate those records having no parent and therefore being the 'root' member. The code is now working with reference to the temp table so I have my results set, but I don't understand why it doesn't run with the multiple CTE approach. Maybe I will ask that in another question. Thanks for your help – Neil Rutland Oct 24 '14 at 11:21

Creating a recursive CTE with no rootrecord

1 Answers1

Linked