SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
When you need a quick tally (SQL Server 2k8)

 
Reply to topic    SoftTree Technologies Forum Index » Tips & Snippets Repository View previous topic
View next topic
When you need a quick tally (SQL Server 2k8)
Author Message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post When you need a quick tally (SQL Server 2k8) Reply with quote
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.

Code:

$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 View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7841

Post Reply with quote
There might be some syntax error around "(SELECT NULL)) AS n"

When trying that snippet, I get
Code:
( 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 View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
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 View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7841

Post Reply with quote
That sounds interesting. Can you provide an example for each described usage type?
Tue May 15, 2012 10:00 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
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.

Code:

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:

Code:

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
Code:

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.
Code:

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 View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » Tips & Snippets Repository All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
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


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.