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 |
|
mavershang
Posting Yak Master
111 Posts |
 Posted - 2010-05-12 : 13:03:21
|
| Hi there. I am new to SQL and have a problem with Common table expression. Here is the query----------------------;With rc1 as( Select Base1, 1 as a From #B Union all Select Base1, a+1 From rc1 Where a < @RepeatShuffling),rc2 as( Select Base2, 1 as b From #B Union all Select Base2, b+1 From rc2 Where b < @RepeatShuffling) Select R1.Base1, R2.Base2, R1.ShufflingTag1, R2.ShufflingTag2, R1.a, R2.bInto #TFrom( Select Base1, row_number() over (partition by a order by NewID()) as ShufflingTag1, aFrom rc1 Option (Maxrecursion 1000) ****************** Here is the problem *************) R1Inner Join ( Select Base2, row_number() over (partition by b order by NewID()) as ShufflingTag2, b From rc2 Option (Maxrecursion 1000) ********* Here is the problem *************** ) R2 On R1.a = R2.b and R1.ShufflingTag1 = R2.ShufflingTag2 Order by R1.a, R1.ShufflingTag1------------------------------------------------As indicated using **** in the above query, without those "Option (Maxrecursion 1000)", everything works fine. But after adding them, error pumps out saying "incorrect syntax".Anyone can help me out? Thanks in advance. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-05-12 : 13:11:24
|
| you cant use them inside CTE as sugested earlier. i really cant make out what you're trying to do with above code------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
mavershang
Posting Yak Master
111 Posts |
Posted - 2010-05-12 : 14:32:51
|
Well. It is for the purpose of research, not for database management.quote:
Originally posted by visakh16
you cant use them inside CTE as sugested earlier. i really cant make out what you're trying to do with above code------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-05-12 : 14:41:30
|
Just a guess:put that option in a new line so sql doesn't think it should be an alias.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-05-13 : 10:16:59
|
quote:
Originally posted by mavershang
Well. It is for the purpose of research, not for database management.quote:
Originally posted by visakh16
you cant use them inside CTE as sugested earlier. i really cant make out what you're trying to do with above code------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
even then wats the scenario?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Query help.