SQL RECURSIVE TABLE INFINITE LOOP

Question

I have a table of companies, with three columns: holder_co_id, held_co_id, pct_owned.

holder_co_id  held_co_id  pct_owned
A             B           25
A             C           50
B             C           50
C             B           10

So my query identifies that A holds 25% of B, A holds 50% of C and A holds a further 5% of B through it's 50% holding in C and a further 12.5% of C through the holding in B.

I was super super happy with myself as for once I'd nailed some sql. This did not last long. I noticed that with the mutual ownership of B and C, this sends the query into an infinite loop. I have tried stopping the loop by putting a max on the loops, but this gives ownerships in some cases still above 100%... besides, it's just not correct.

Any ideas on how else I would do this other than:

WITH Result AS 
(SELECT HOLDER_CO_ID, 
        HELD_CO_ID, 
        PCT_OWNED, 
        1 AS generationsremoved
FROM dbo.ALL_HOLDING_INFO
UNION ALL
SELECT  P.HOLDER_CO_ID, 
        N.HELD_CO_ID, 
        P.PCT_OWNED * N.PCT_OWNED AS Expr1,
        P.generationsremoved + 1 AS Expr2
FROM  Result AS P INNER JOIN
dbo.ALL_HOLDING_INFO AS N ON P.HELD_CO_ID = N.HOLDER_CO_ID
WHERE     (P.generationsremoved <= 10))
SELECT DISTINCT TOP (100) PERCENT HOLDER_CO_ID, 
                                  HELD_CO_ID, 
                                  PCT_OWNED, 
                                  generationsremoved
 FROM         Result AS Result_1
 WHERE     (PCT_OWNED > 0.005)
 ORDER BY HOLDER_CO_ID, HELD_CO_ID, generationsremoved DESC

Pretty sure the way to fix this... would be to remove the infinite loop. — Sam DeHaan
– Sam DeHaan, Commented Apr 2, 2014 at 20:47
It's perfectly possible (although probably not legal) to have percentages greater than %100 (a la The Producers). Are you sure there's a viable "minimum percentage owned"? You may need to at least be able to effectively record percentages equivalent to 1 share (and there are probably some real-world situations that result in fractions of a share...) — Clockwork-Muse
– Clockwork-Muse, Commented Apr 2, 2014 at 21:51

SoulTrain · Accepted Answer · 2014-04-02 20:53:23Z

2

Try stopping your iteration on the Recursive block of your CTE instead of doing it outside, my guess is if you stop it while SQL is trying to build it (inside the recursive block) it wont get into a infinite loop

  WITH Result AS 
    (SELECT HOLDER_CO_ID, 
            HELD_CO_ID, 
            PCT_OWNED, 
            1 AS generationsremoved
    FROM dbo.ALL_HOLDING_INFO
    UNION ALL
    SELECT  P.HOLDER_CO_ID, 
            N.HELD_CO_ID, 
            P.PCT_OWNED * N.PCT_OWNED AS Expr1,
            P.generationsremoved + 1 AS Expr2
    FROM  Result AS P INNER JOIN
    dbo.ALL_HOLDING_INFO AS N ON P.HELD_CO_ID = N.HOLDER_CO_ID
    WHERE     (P.generationsremoved<= 10)
      AND (PCT_OWNED > 0.005))
    SELECT DISTINCT TOP (100) PERCENT HOLDER_CO_ID, 
                                      HELD_CO_ID, 
                                      PCT_OWNED, 
                                      generationsremoved
     FROM         Result AS Result_1

edited Apr 2, 2014 at 20:53

answered Apr 2, 2014 at 20:47

SoulTrain

1,9041 gold badge12 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Eric J. Price Over a year ago

The PCT_OWNED that is limiting to greater than 0.005 is ambiguous. Can you alias it with the desired table?

Eric J. Price Over a year ago

I don't think this accomplishes the real world goal the OP is going for as this will still continue the loop depth that is undesired and return irrelevant data. If C owns 50% of B and B own 50% of C then they both own 50% of eachother they don't own 50% + 25% + 12.5% + 6.25% + ... of eachother.

Monsieur Mark Over a year ago

Love2Learn's correct, have tried this and I get the same infinite loop. I should not have included "AND (PCT_OWNED > 0.005)" in the code posted, just another route I had tried and forgot to take out for post. Soz.

Eric J. Price · Accepted Answer · 2014-04-02 21:17:25Z

1

This will give you what you want. It first creates a hierarchy and then iterates through it and finally sums the aggregates to get you the total holding. As long as neither the HOLDER_CO_ID or HELD_CO_ID have a | in them this will work...

/*
If      Object_ID('dbo.ALL_HOLDING_INFO') Is Not Null Drop Table dbo.ALL_HOLDING_INFO
Create  Table dbo.ALL_HOLDING_INFO (HOLDER_CO_ID Varchar(10), HELD_CO_ID Varchar(10), PCT_OWNED Float)

Insert  dbo.ALL_HOLDING_INFO
Values ('A', 'B', .25),('A', 'C', .50),('B' ,'C', .50),('C','B',.10);
*/


With    Hierarchy As
(
        Select  Distinct HOLDER_CO_ID As HOLDER_CO_ID, 
                HELD_CO_ID As HELD_CO_ID,
                PCT_OWNED,
                Convert(Varchar(Max),'|' + HELD_CO_ID + '|') As AllChildren, 
                0 As RelationDepth
        From    dbo.ALL_HOLDING_INFO
        Union   All
        Select  h.HOLDER_CO_ID, 
                a.HELD_CO_ID As HELD_CO_ID,
                a.PCT_OWNED,
                Convert(Varchar(Max),h.AllChildren + a.HELD_CO_ID) + '|' As AllChildren, 
                h.RelationDepth + 1
        From    Hierarchy h
        Join    dbo.ALL_HOLDING_INFO a
                On  h.HELD_CO_ID = a.HOLDER_CO_ID
        Where   h.HOLDER_CO_ID != a.HELD_CO_ID
        And     h.AllChildren Not Like '%|' + a.HELD_CO_ID + '|%'
), Result AS 
(
        SELECT  Row_Number() Over (Order By Holder_CO_ID, PCT_OWNED) As tID,
                HOLDER_CO_ID, 
                HELD_CO_ID, 
                PCT_OWNED, 
                RelationDepth
        FROM    Hierarchy
        Where   RelationDepth = 0
        UNION   ALL
        SELECT  p.tID,
                P.HOLDER_CO_ID, 
                N.HELD_CO_ID, 
                P.PCT_OWNED * N.PCT_OWNED AS Expr1,
                n.RelationDepth
        FROM    Result AS P 
        JOIN    Hierarchy AS N 
                ON  P.HOLDER_CO_ID = N.HOLDER_CO_ID
                And p.HELD_CO_ID != n.HELD_CO_ID
                And p.RelationDepth = n.RelationDepth - 1
)
SELECT  HOLDER_CO_ID, HELD_CO_ID, SUM(PCT_OWNED)*100 As PCT_OWNED                             
FROM    Result AS Result_1
Group   By HOLDER_CO_ID, HELD_CO_ID
ORDER   BY HOLDER_CO_ID, HELD_CO_ID DESC

answered Apr 2, 2014 at 21:17

Eric J. Price

2,7851 gold badge17 silver badges22 bronze badges

2 Comments

Eric J. Price Over a year ago

PS: If you want the individual chunks simply take out the aggregation logic in the final select at the bottom.

Monsieur Mark Over a year ago

OOOOO so close! I thought the above was perfect til I noticed that company C is owned 112.5% in total :-) I'll take a closer look, see if I made a mistake. building a hierarchy first - this is definitely the way to go, thx Love2learn

Collectives™ on Stack Overflow

SQL RECURSIVE TABLE INFINITE LOOP

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related