Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AlexR
Starting Member
2 Posts |
 Posted - 2009-05-20 : 19:15:39
|
| Say I want to follow the result of a function thru N iterations (X[N]), where the initial value of iteration N is the resulting value of iteration N-1. For iteration N=1 the initial value is found in a table, but for this discussion let’s just assume it is known. So, all we know is X[0] and the function. Say the function in 2*(X[N-1]) and X[0]=3, then I would like the query to return:Iteration InitialValue EndValue1 3 62 6 123 12 24… … …The number of iterations is finite and known.Sounds simple enough, but can I do it with an SQL statement?Thank you for your time! |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-05-21 : 04:18:46
|
| How many iterations max?A Common table expression can do what you want in a (?nice?) recursive way but there is a maximum recursion level of 32,767Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-05-21 : 04:21:14
|
| Ah -- I've just seen that you've posted in the MS access forumdon't think you can use a CTE in access.Please ignore my ramblings if you are actually using access.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
AlexR
Starting Member
2 Posts |
Posted - 2009-05-21 : 11:15:47
|
| Thanks for your replies. Not that it matters (since you've said the function s unavailable in Access), but the # of iterations would be ~ 40.Alex |
|
|
|
|
|
|
|
|
SQL Help... Recursive Query