CODEWITHSUNDEEP
sqlsql-serverrecursioncommon-table-expression
dstewart101
dstewart101

Reputation: 1112

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.

Upvotes: 1

Views: 177

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72185

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.

Upvotes: 2

Related Questions