May 23, 2013 at 12:58 am
todd.ayers (5/22/2013)
Well,I have finally had a chance to test this and I keep getting an error message saying:
Msg 102, Level 15, State 1, Line 53
Incorrect syntax near ')'.
I don't see anything wrong with the code though
What version of SQL Server are you connecting to, Todd?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 23, 2013 at 1:01 am
dwain.c (5/22/2013)
...That is a brilliant solution Chris!
And here I thought when I pulled up this thread I could make a shameless plug for my random numbers article (in my signature).
Thanks by the way for the nice plug in your signature for my rCTEs article.
Thanks Dwain! I get a warm fuzzy feeling inside when I write a query which you can tell at a glance what it's supposed to do 😉
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 23, 2013 at 5:02 am
I am running SQL Server 2012
May 23, 2013 at 5:10 am
todd.ayers (5/22/2013)
Well,I have finally had a chance to test this and I keep getting an error message saying:
Msg 102, Level 15, State 1, Line 53
Incorrect syntax near ')'.
I don't see anything wrong with the code though
I get the same error message if I comment out the last line of the query:
Msg 102, Level 15, State 1, Line 53
Incorrect syntax near ')'.
😉
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 23, 2013 at 5:25 am
Well.... It's good to know that I'm not doing something wrong then.....
Btw what do you mean when you say "if I comment out the last line"?
May 23, 2013 at 5:41 am
if you have a comment mark before the last line
eg --) l9 rather than just ) l9
If I comment out the last line, I get the same error you do, but if not, it runs just fine (2008 R2)
Bex
May 23, 2013 at 5:42 am
Nice solution btw, Chris.
Bex
May 23, 2013 at 5:56 am
Bex (5/23/2013)
if you have a comment mark before the last lineeg --) l9 rather than just ) l9
If I comment out the last line, I get the same error you do, but if not, it runs just fine (2008 R2)
Bex
You'd get the same error if the last line was accidentally missed by copy'n'paste.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 23, 2013 at 5:56 am
Bex (5/23/2013)
Nice solution btw, Chris.Bex
Gosh, thanks! :blush:
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 24, 2013 at 8:53 am
ChrisM@Work (5/23/2013)
Bex (5/23/2013)
Nice solution btw, Chris.Bex
Gosh, thanks! :blush:
It truly is an elegant solution Chris! Kudos.
However, I will nit-pick on using sys.columns for the driver. 😎 That gets you a double-hit on syscolpars per iteration, leading to 66 page reads. Using a virtual numbers table gets you zero cpu, duration (like your query) AND zero reads. I tried it a bunch of times and it worked without the 10K iteration, but that could be added in if desired.
;WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b)--, --10E+2 or 100 rows
-- E4(N) AS (SELECT 1 FROM E2 a, E2 b) --10E+4 or 10,000 rows max
, SemiRandomNumbers AS (
SELECT Lane = 8 + ABS(CHECKSUM(NEWID()))%41
FROM E2
)
SELECT *
FROM (SELECT ID = 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) s
CROSS APPLY (
SELECT TOP 1 Lane1 = Lane FROM SemiRandomNumbers --ORDER BY (SELECT NULL)
) l1
CROSS APPLY (
SELECT TOP 1 Lane2 = Lane FROM SemiRandomNumbers r
WHERE NOT (r.Lane BETWEEN l1.Lane1-5 AND l1.Lane1+5)
) l2
CROSS APPLY (
SELECT TOP 1 Lane3 = Lane FROM SemiRandomNumbers r
WHERE NOT (r.Lane BETWEEN l1.Lane1-5 AND l1.Lane1+5)
AND NOT (r.Lane BETWEEN l2.Lane2-5 AND l2.Lane2+5)
) l3
CROSS APPLY (
SELECT TOP 1 Lane4 = Lane FROM SemiRandomNumbers r
WHERE NOT (r.Lane BETWEEN l1.Lane1-5 AND l1.Lane1+5)
AND NOT (r.Lane BETWEEN l2.Lane2-5 AND l2.Lane2+5)
AND NOT (r.Lane BETWEEN l3.Lane3-5 AND l3.Lane3+5)
) l4
CROSS APPLY (
SELECT TOP 1 Lane5 = Lane FROM SemiRandomNumbers r
WHERE NOT (r.Lane BETWEEN l2.Lane2-5 AND l2.Lane2+5)
AND NOT (r.Lane BETWEEN l3.Lane3-5 AND l3.Lane3+5)
AND NOT (r.Lane BETWEEN l4.Lane4-5 AND l4.Lane4+5)
) l5
CROSS APPLY (
SELECT TOP 1 Lane6 = Lane FROM SemiRandomNumbers r
WHERE NOT (r.Lane BETWEEN l3.Lane3-5 AND l3.Lane3+5)
AND NOT (r.Lane BETWEEN l4.Lane4-5 AND l4.Lane4+5)
AND NOT (r.Lane BETWEEN l5.Lane5-5 AND l5.Lane5+5)
) l6
CROSS APPLY (
SELECT TOP 1 Lane7 = Lane FROM SemiRandomNumbers r
WHERE NOT (r.Lane BETWEEN l4.Lane4-5 AND l4.Lane4+5)
AND NOT (r.Lane BETWEEN l5.Lane5-5 AND l5.Lane5+5)
AND NOT (r.Lane BETWEEN l6.Lane6-5 AND l6.Lane6+5)
) l7
CROSS APPLY (
SELECT TOP 1 Lane8 = Lane FROM SemiRandomNumbers r
WHERE NOT (r.Lane BETWEEN l5.Lane5-5 AND l5.Lane5+5)
AND NOT (r.Lane BETWEEN l6.Lane6-5 AND l6.Lane6+5)
AND NOT (r.Lane BETWEEN l7.Lane7-5 AND l7.Lane7+5)
) l8
CROSS APPLY (
SELECT TOP 1 Lane9 = Lane FROM SemiRandomNumbers r
WHERE NOT (r.Lane BETWEEN l6.Lane6-5 AND l6.Lane6+5)
AND NOT (r.Lane BETWEEN l7.Lane7-5 AND l7.Lane7+5)
AND NOT (r.Lane BETWEEN l8.Lane8-5 AND l8.Lane8+5)
) l9
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 24, 2013 at 11:23 am
Chris: Nice work.
Todd: Welcome to the life post-Access. I think you'll find a lot more power and flexibility here than in your previous life. Warning - you'll probably never want to go back. 😀
May 28, 2013 at 6:04 am
Actually I am still working on understanding how to move over to SQL Server from access. I want to move the tables and leave the forms and reports as access.... I am learning that there is a lot involved...
June 3, 2013 at 7:09 am
TheSQLGuru (5/24/2013)
ChrisM@Work (5/23/2013)
Bex (5/23/2013)
Nice solution btw, Chris.Bex
Gosh, thanks! :blush:
It truly is an elegant solution Chris! Kudos.
However, I will nit-pick on using sys.columns for the driver. 😎 That gets you a double-hit on syscolpars per iteration, leading to 66 page reads. Using a virtual numbers table gets you zero cpu, duration (like your query) AND zero reads. I tried it a bunch of times and it worked without the 10K iteration, but that could be added in if desired.
;WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b)--, --10E+2 or 100 rows
-- E4(N) AS (SELECT 1 FROM E2 a, E2 b) --10E+4 or 10,000 rows max
, SemiRandomNumbers AS (
SELECT Lane = 8 + ABS(CHECKSUM(NEWID()))%41
FROM E2
)
SELECT *
FROM (SELECT ID = 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) s
CROSS APPLY (
SELECT TOP 1 Lane1 = Lane FROM SemiRandomNumbers --ORDER BY (SELECT NULL)
) l1
CROSS APPLY (
SELECT TOP 1 Lane2 = Lane FROM SemiRandomNumbers r
WHERE NOT (r.Lane BETWEEN l1.Lane1-5 AND l1.Lane1+5)
) l2
CROSS APPLY (
SELECT TOP 1 Lane3 = Lane FROM SemiRandomNumbers r
WHERE NOT (r.Lane BETWEEN l1.Lane1-5 AND l1.Lane1+5)
AND NOT (r.Lane BETWEEN l2.Lane2-5 AND l2.Lane2+5)
) l3
CROSS APPLY (
SELECT TOP 1 Lane4 = Lane FROM SemiRandomNumbers r
WHERE NOT (r.Lane BETWEEN l1.Lane1-5 AND l1.Lane1+5)
AND NOT (r.Lane BETWEEN l2.Lane2-5 AND l2.Lane2+5)
AND NOT (r.Lane BETWEEN l3.Lane3-5 AND l3.Lane3+5)
) l4
CROSS APPLY (
SELECT TOP 1 Lane5 = Lane FROM SemiRandomNumbers r
WHERE NOT (r.Lane BETWEEN l2.Lane2-5 AND l2.Lane2+5)
AND NOT (r.Lane BETWEEN l3.Lane3-5 AND l3.Lane3+5)
AND NOT (r.Lane BETWEEN l4.Lane4-5 AND l4.Lane4+5)
) l5
CROSS APPLY (
SELECT TOP 1 Lane6 = Lane FROM SemiRandomNumbers r
WHERE NOT (r.Lane BETWEEN l3.Lane3-5 AND l3.Lane3+5)
AND NOT (r.Lane BETWEEN l4.Lane4-5 AND l4.Lane4+5)
AND NOT (r.Lane BETWEEN l5.Lane5-5 AND l5.Lane5+5)
) l6
CROSS APPLY (
SELECT TOP 1 Lane7 = Lane FROM SemiRandomNumbers r
WHERE NOT (r.Lane BETWEEN l4.Lane4-5 AND l4.Lane4+5)
AND NOT (r.Lane BETWEEN l5.Lane5-5 AND l5.Lane5+5)
AND NOT (r.Lane BETWEEN l6.Lane6-5 AND l6.Lane6+5)
) l7
CROSS APPLY (
SELECT TOP 1 Lane8 = Lane FROM SemiRandomNumbers r
WHERE NOT (r.Lane BETWEEN l5.Lane5-5 AND l5.Lane5+5)
AND NOT (r.Lane BETWEEN l6.Lane6-5 AND l6.Lane6+5)
AND NOT (r.Lane BETWEEN l7.Lane7-5 AND l7.Lane7+5)
) l8
CROSS APPLY (
SELECT TOP 1 Lane9 = Lane FROM SemiRandomNumbers r
WHERE NOT (r.Lane BETWEEN l6.Lane6-5 AND l6.Lane6+5)
AND NOT (r.Lane BETWEEN l7.Lane7-5 AND l7.Lane7+5)
AND NOT (r.Lane BETWEEN l8.Lane8-5 AND l8.Lane8+5)
) l9
Thanks Kevin, you're absolutely right.
It's quicker to write FROM x a, x b than it is to track down the inline tally table code. Gosh, it's only a couple of weeks ago since I moaned about poor answers on the watercooler thread :blush:
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 3, 2013 at 7:18 am
It's quicker to write FROM x a, x b than it is to track down the inline tally table code.
That's what Snippets are for silly! 😀 Actually for me it is my Clip Library in TextPad ... what a wonderful tool.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 3, 2013 at 8:33 am
TheSQLGuru (6/3/2013)
It's quicker to write FROM x a, x b than it is to track down the inline tally table code.
That's what Snippets are for silly! 😀 Actually for me it is my Clip Library in TextPad ... what a wonderful tool.
That's 2k12, haven't had much opportunity to play with it yet.
At home things are different, I have a db set aside as a container for useful code. Probably not as quick to search & action as your Clip Library but I'm used to it.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply