dstewart101 dstewart101 - 6 months ago 8
SQL Question

CTE - recursive query doing too much

I have the current table of data...

| LoanRollupID | NewLoanID | PreviousLoanID |
|--------------|-----------|----------------|
| 11 | 76 | 44 |
| 12 | 80 | 75 |
| 13 | 83 | 82 |
| 14 | 84 | 83 |
| 15 | 86 | 85 |
| 16 | 87 | 54 |
| 17 | 88 | 87 |
| 18 | 90 | 48 |
| 19 | 91 | 34 |
| 20 | 93 | 41 |
| 21 | 94 | 76 |
| 22 | 95 | 90 |
| 23 | 96 | 94 |
| 24 | 100 | 92 |
| 25 | 101 | 99 |
| 26 | 102 | 98 |
| 27 | 103 | 101 |
| 28 | 104 | 81 |
| 29 | 105 | 80 |
| 30 | 107 | 52 |
| 31 | 110 | 108 |
| 1029 | 1105 | 103 |
| 1030 | 1106 | 104 |
| 1031 | 1108 | 1106 |
| 1032 | 1109 | 73 |


I'm trying to jump in at NewLoanID 1108 and see how it has evolved from previous Loans. e.g 1108 came from 1106, which came from 104, which came from 81, etc.

When I run this query:

WITH OldLoans (PreviousLoanID, NewLoanID, start)
AS
(
---- Anchor member definition
SELECT l.NewLoanID, l.PreviousLoanID, 0 as start
FROM dscs_public.LoanRollup l
Where NewLoanID = 1108
UNION ALL
-- Recursive member definition
SELECT l.NewLoanID, l.PreviousLoanID, start + 1
FROM dscs_public.LoanRollup l
INNER JOIN OldLoans AS o
ON o.NewLoanID = l.PreviousLoanID
)
---- Statement that executes the CTE
SELECT PreviousLoanID, NewLoanID, start
FROM OldLoans


It fails with this error:


The statement terminated. The maximum recursion 100 has been exhausted
before statement completion.


Can anyone spot my mistake please?
Thanks.

Answer

The aliases in the CTE definition are in the wrong order:

-- Instead of (PreviousLoanID, NewLoanID, start)
WITH OldLoans (NewLoanID, PreviousLoanID, start)
AS
(
   ---- Anchor member definition
   SELECT l.NewLoanID, l.PreviousLoanID, 0 as start
   FROM mytable l --LoanRollup l
   Where NewLoanID = 1108

   UNION ALL

   -- Recursive member definition
   SELECT l.NewLoanID, l.PreviousLoanID, start + 1
   FROM mytable l --dscs_public.LoanRollup l
   INNER JOIN OldLoans AS o
       -- Instead of o.NewLoanID = l.PreviousLoanID
       ON l.NewLoanID = o.PreviousLoanID

)
---- Statement that executes the CTE
SELECT PreviousLoanID, NewLoanID, start
FROM OldLoans

The same thing holds for the ON clause in the recursive member definition.