NEED HELP WITH RECURSIVE CTE

Question

Post reply

NEED HELP WITH RECURSIVE CTE

SWATH

Old Hand

Points: 303
More actions
December 12, 2012 at 5:40 am

#286087

hi ALL,
;WITH CTE_BALANCE(SLNO,RECEIVED,PAID,BALANCE)
AS
(
SELECT slno,received,paid,CAST(ISNULL(BALANCE,0) AS NVARCHAR(64))
FROM #TEMP WHERE SLNO=1
UNION ALL
SELECT t1.slno,t1.received,t1.paid,CAST((ISNULL(T1.RECEIVED,0) - ISNULL(T1.PAID,0)) + ISNULL(T2.BALANCE,0) AS NVARCHAR(64)) BALANCE
FROM #TEMP as T1
JOIN CTE_BALANCE AS T2 ON t1.slno-1 = t2.slno
)
UPDATE #TEMP
SET BALANCE = T1.BALANCE
FROM
#TEMP AS T2
INNER JOIN
CTE_BALANCE
AS T1 ON t1.slno = t2.slno;
FROM THIS CODE I AM GETTING ERROR AS
Msg 530, Level 16, State 1, Procedure USP_Report_CashBookBookDetailSummary_Select, Line 142
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
TO SOLVE THIS ERROR I NEED TO KEEP OPTION (MAXRECURSION 1000)
BUT WHERE CAN I ADD OPTION (MAXRECURSION 1000) ?????????
PLEASE HELP ME

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

nigelrivett SSCertifiable Points: 5472 More actions · Answer 1

At the end of the statement

Cursors never.
DTS - only when needed and never to control.

SWATH Old Hand Points: 303 More actions · Answer 2

SWATH

Old Hand

Points: 303

December 12, 2012 at 5:44 am

#1567836

i have tried bt no use:((

nigelrivett SSCertifiable Points: 5472 More actions · Answer 3

create table #TEMP (slno int, received int, BALANCE int, paid int)

;WITH CTE_BALANCE(SLNO,RECEIVED,PAID,BALANCE)

AS

(

SELECT slno,received,paid,CAST(ISNULL(BALANCE,0) AS NVARCHAR(64))

FROM #TEMP WHERE SLNO=1

UNION ALL

SELECT t1.slno,t1.received,t1.paid,CAST((ISNULL(T1.RECEIVED,0) - ISNULL(T1.PAID,0)) + ISNULL(T2.BALANCE,0) AS NVARCHAR(64)) BALANCE

FROM #TEMP as T1

JOIN CTE_BALANCE AS T2 ON t1.slno-1 = t2.slno

)

UPDATE #TEMP

SET BALANCE = T1.BALANCE

FROM

#TEMP AS T2

INNER JOIN

CTE_BALANCE

AS T1 ON t1.slno = t2.slno

option (maxrecursion 1000);

Cursors never.
DTS - only when needed and never to control.

SWATH Old Hand Points: 303 More actions · Answer 4

Thank you so much ..what if my table has more than 32,767 rows????????

nigelrivett SSCertifiable Points: 5472 More actions · Answer 5

option (maxrecursion 0);

Will keep going.

Cursors never.
DTS - only when needed and never to control.

CapnHector SSCoach Points: 16143 More actions · Answer 6

This looks like a running total (from the math in the rCTE). Have a look at Jeff Modens article on the "Quirky Update" http://www.sqlservercentral.com/articles/T-SQL/68467/ It will at least give you some options and is blazing fast compared to the previous recursive methods (either rCTE or Cursor). However you need to read and understand the article thoroughly before you use it as if you don't get things exact you can really mess things up. (personal experience when playing with the method and seeing if his warnings were true lets me know they are.)

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

Alan Burstein SSC Guru Points: 61141 More actions · Answer 7

CELKO (12/12/2012)
Read about the Window clause, which makes it very easy to write running totals:
CREATE VIEW Running_Balance (payment_nbr, receipt_date, payment_amt, acct_balance)
AS
SELECT payment_nbr, receipt_date, payment_amt,
SUM(payment_amt)
OVER (ORDER BY receipt_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM Payment_Journal;

Rows Window frame extensions are not available in SQL 2008.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Alan Burstein SSC Guru Points: 61141 More actions · Answer 8

capnhector (12/12/2012)
This looks like a running total (from the math in the rCTE). Have a look at Jeff Modens article on the "Quirky Update" http://www.sqlservercentral.com/articles/T-SQL/68467/ It will at least give you some options and is blazing fast compared to the previous recursive methods (either rCTE or Cursor). However you need to read and understand the article thoroughly before you use it as if you don't get things exact you can really mess things up. (personal experience when playing with the method and seeing if his warnings were true lets me know they are.)

The rCTE often (and unjustifiably) gets thrown into the same category of iterative methods as a cursor or loop. The rCTE is more of a hybrid of set-based and iterative logic and, in the case of running total, the only faster solution using TSQL than the rCTE is the aforementioned Quirky Update.

I have not done much with running totals in SQL 2012 but I have found the solution using frame delimiters (CURRENT ROW, UNBOUNDED...) to be the fastest but I have not tested it against the QU method.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001