![SoftTree Technologies](templates/GreenTech/images/logo.gif) |
SoftTree Technologies
Technical Support Forums
|
|
Author |
Message |
gemisigo
Joined: 11 Mar 2010 Posts: 2109
|
|
When you need a quick tally (SQL Server 2k8) |
|
In case you're in urgent need for a quick single use tally and don't want to bother creating it, you can use this snippet as a subquery.
![](templates/GreenTech/images/code_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
$PROMPT(rowcount,How many rows do you need?,48879)$
( SELECT TOP $rowcount$ ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM [master].dbo.SysColumns sc1 CROSS JOIN [master].dbo.SysColumns sc2) AS qt_$rowcount$
|
|
|
Tue May 15, 2012 9:32 am |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7854
|
|
|
|
There might be some syntax error around "(SELECT NULL)) AS n"
When trying that snippet, I get
![](templates/GreenTech/images/code_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
( SELECT TOP 48879 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM [master].dbo.SysColumns sc1 CROSS JOIN [master].dbo.SysColumns sc2) AS qt_48879 |
and that query doesn't compile.
Last edited by SysOp on Tue May 15, 2012 10:04 am; edited 1 time in total |
|
Tue May 15, 2012 9:47 am |
|
![](templates/GreenTech/images/spacer.gif) |
gemisigo
Joined: 11 Mar 2010 Posts: 2109
|
|
|
|
It is intended to work as a subquery (or a subquery table to be more precise), it does not run on its own. You have to put 'SELECT n FROM' in front of the snippet result to make it work in its most simple form. I mostly use it when I have to populate a table with temporary test data or just need a table with sequential numbers in it. Also comes in handy when cutting strings.
|
|
Tue May 15, 2012 9:57 am |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7854
|
|
|
|
That sounds interesting. Can you provide an example for each described usage type?
|
|
Tue May 15, 2012 10:00 am |
|
![](templates/GreenTech/images/spacer.gif) |
gemisigo
Joined: 11 Mar 2010 Posts: 2109
|
|
|
|
Sure.
The first case:
One of our customers has a database that contains a structure about access cards. The cards are assigned to levels of towers. There are 3 towers, each of them having 8 levels. One of our developer was tasked to create a query that tells which levels and towers is a card assigned to. We had the structure that stores assignments but it was completely empty. So I had to produce some test data. While it is very far from real life situation, the following script fills that table. I did not bother adding some conditions so that it resembles the real life data more precisely but it would be easy.
![](templates/GreenTech/images/code_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
SELECT
qt_3.n AS [tower]
,qt_8.n AS [level]
,qt_3.n * qt_20.n AS [card]
FROM
( SELECT TOP 3 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM [master].dbo.SysColumns sc1 CROSS JOIN [master].dbo.SysColumns sc2) AS qt_3
CROSS JOIN
( SELECT TOP 8 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM [master].dbo.SysColumns sc1 CROSS JOIN [master].dbo.SysColumns sc2) AS qt_8
CROSS JOIN
( SELECT TOP 20 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM [master].dbo.SysColumns sc1 CROSS JOIN [master].dbo.SysColumns sc2) AS qt_20
|
The second case:
Another customer has a system that stores doorkeys assigned to a card in a very uncomfortable way. They store it in a varchar fields, each key separated by ':'. Don't ask why, it's a legacy system, it has caused ungodly amount of trouble so far. They have thousands of employees (dolgozók in Hungarian), and several keys (kulcsok) assigned to many of them. There were no checks on the 3 fields (UF4, UF18, UF19) containing the keys but they were required to contain only key positions (kulcs pozíciók) and the separator and we had to indicate if there was an error (hiba in Hungarian). The original table looked like this:
![](templates/GreenTech/images/code_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
id_dolgozo | UF4 | UF18 | UF19 | etc.
1 | 3 | | 3:22 | ;notice the duplicates here
2 | | 33:333 | a32:90 | ;notice the invalid data here
3 | 39:22 | 4 | |
|
We created a query that transformed this mess into a view
![](templates/GreenTech/images/code_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
id_dolgozo | kulcs_pozico | hiba | etc
1 | 3 | 0
1 | 22 | 0
2 | 33 | 0
2 | 333 | 0
2 | a32 | 1
etc.
|
It worked. And the performance was a nightmare. Until the query was replaced with the following below which does not use a loop to locate the boundaries. It's still of moderate speed but it's faster by orders of magnitude.
![](templates/GreenTech/images/code_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
SELECT
kl.id_dolgozo
,CAST(SUBSTRING(kl.kulcsok ,N + 1 ,CHARINDEX(':' ,kl.kulcsok ,N + 1) -N -1) as SMALLINT) AS kulcs_pozicio
,CASE
WHEN PATINDEX('%[^0-9:]%' ,SUBSTRING(kl.kulcsok ,N + 1 ,CHARINDEX(':' ,kl.kulcsok ,N + 1) -N -1)) > 0 THEN SUBSTRING(kl.kulcsok ,N + 1 ,CHARINDEX(':' ,kl.kulcsok ,N + 1) -N -1)
ELSE NULL
END AS hiba
FROM
(
SELECT
uf.CardHolderID AS id_dolgozo
,REPLACE(REPLACE(REPLACE(':' + uf .UF4 + ':' + uf.UF18 + ':' + uf.UF19 + ':' ,'::' ,':;') ,';:' ,'') ,':;' ,':') AS kulcsok
FROM
CMS.dbo.UserFields uf
WHERE LTRIM(RTRIM(uf.UF4)) <> ''
OR LTRIM(RTRIM(uf.UF18)) <> ''
OR LTRIM(RTRIM(uf.UF19)) <> ''
OR LTRIM(RTRIM(uf.UF20)) <> ''
) AS kl
CROSS JOIN
dbo.tally AS t
WHERE n < LEN(kl.kulcsok)
AND SUBSTRING(kl.kulcsok ,N ,1) = ':'
|
The tally table here is a physical table the database since it is used recurringly but it was created using that snippet.
|
|
Tue May 15, 2012 10:42 am |
|
![](templates/GreenTech/images/spacer.gif) |
|
|
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
|
|
|