Home Forums SQL Server 2008 SQL Server Newbies Work with CTE when data itself has discrepency(infinite loop. RE: Work with CTE when data itself has discrepency(infinite loop.

  • ah0996 (7/25/2016)


    Hi,

    I need help with my CTE; All I need to find is the match between groupID and memberID and set nodelevel; once the result is already there, I want my CTE to stop. However, it is right now it is going in infinite loop because of 9,8.

    I want my result to look like this:

    groupIDobjectType memberIDNodelevel

    8 group 1 0

    1 group 9 1

    1 user 7 1

    9 group 8 2

    ---CODE---

    Currently, results are going in infinite loop.

    I have Hierarchy table:

    drop table #Hierarchy

    CREATE TABLE #Hierarchy( groupID int , memberID int, objectType varchar(max))

    insert into #Hierarchy ( groupID , memberID , objectType ) values (8, 1,'group')

    insert into #Hierarchy ( groupID , memberID , objectType ) values (1, 9,'group')

    insert into #Hierarchy ( groupID , memberID , objectType ) values (1, 7,'user')

    insert into #Hierarchy ( groupID , memberID , objectType ) values (9, 8,'group')

    WITH CTE( groupID, objectType, memberID, [Nodelevel]) as

    (SELECT tm.groupID

    ,tm.objectType

    , tm.memberID

    , 0 as [Nodelevel]

    from #Hierarchy tm with(nolock) -- drop table #tblGrpsMatc

    where groupID = 8

    UNION ALL

    SELECT

    TC.groupID

    ,TC.objectType

    , TC.memberID

    , [Nodelevel] + 1 as [Nodelevel]

    FROM CTE C2

    inner join #Hierarchy TC with(nolock)

    ON tc.groupID = C2.memberID

    AND TC.groupID <> TC.memberID

    -- AND ( TC.groupID <> C2.groupID AND C2.[Nodelevel] = 0)

    )

    select groupID, objectType, memberID, [Nodelevel] from CTE

    Try this:

    WITH CTE( groupID, objectType, memberID, [Nodelevel], OriginID) as

    (

    SELECT

    tm.groupID

    , tm.objectType

    , tm.memberID

    , 0 as [Nodelevel]

    , tm.groupID

    from #Hierarchy tm with(nolock) -- drop table #tblGrpsMatc

    where groupID = 8

    UNION ALL

    SELECT

    TC.groupID

    ,TC.objectType

    , TC.memberID

    , [Nodelevel] + 1 as [Nodelevel]

    , C2.OriginID

    FROM CTE C2

    inner join #Hierarchy TC with(nolock)

    ON tc.groupID = C2.memberID

    AND TC.groupID <> TC.memberID

    AND C2.OriginID <> TC.groupID

    )

    SELECT groupID, objectType, memberID, [Nodelevel]

    FROM CTE;

    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