Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Identity issue again

Posted on 2004-10-07
8
Medium Priority
?
246 Views
Last Modified: 2010-04-17
Hi again,

Hope you can help me again
I have the following dynamic SQL statement

     EXEC('SELECT
                  u.USER
                  , IDENTITY(INT, 1, 1) AS '+''''+@Rank+''''+'
                  , ISNULL(tr.'+@Day_Name+'TOTALR, 0) AS '+''''+@TotalR+''''+'
                  ,ISNULL(tr'+@Day_Name+'TOTALP, 0) AS '+''''+@TotalP+''''+'
                  INTO '+ @TotalTable+'
                  FROM ##TOTAL '+@Day_Name+' tr
                  RIGHT JOIN #Temp_Users u ON tr.UserID = u.UserId
                  order by '+@TotalP+' DESC')

This is what it would look like if you ran it normally

           SELECT
                 u.User
                  , IDENTITY(INT, 1, 1) AS MONDAYRANK
                  , ISNULL(tr.MONDAYTOTALR, 0) AS MONDAYTOTALR
                  ,ISNULL(tr.MONDAYTOTALP, 0) AS MONDAYTOTALP
                  INTO #TEST
                  FROM ##TOTALMONDAY tr
                  RIGHT JOIN #Temp_Users u ON tr.UserID = u.UserId
                  order by MONDAYTOTALP DESC

The problem with the dynamic query is it doesn't return the correct results in the identity column.
The sorting seems all jumbled.
Whereas the original returns the correct results.

Any suggestions??



0
Comment
Question by:azrakdragon
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
8 Comments
 
LVL 59

Expert Comment

by:Julian Hansen
ID: 12251558
When you say it does not return the correct results in the IDENTITY column - can you be a bit more specific.
0
 
LVL 6

Expert Comment

by:vadim_ti
ID: 12253076
try this way

     EXEC('SELECT
                  u.USER
                  , IDENTITY(INT, 1, 1) AS '+''''+@Rank+''''+'
                  , ISNULL(tr.'+@Day_Name+'TOTALR, 0) AS '+''''+@TotalR+''''+'
                  ,ISNULL(tr'+@Day_Name+'TOTALP, 0) AS '+''''+@TotalP+''''+'
                  INTO '+ @TotalTable+'
                  FROM ##TOTAL '+@Day_Name+' tr
                  RIGHT JOIN #Temp_Users u ON tr.UserID = u.UserId
                  order by ISNULL(tr'+@Day_Name+'TOTALP, 0)  DESC')

0
 

Author Comment

by:azrakdragon
ID: 12256228
Hi

Julian, when I say it gives the incorrect results, i meant it was sorting the data incorrectly

i.e.

1      0      0
2      0      0
3      0      0
4      0      0
5      0      0
6      0      0
7      0      0
8      0      0
9      0      0
10      0      0
11      0      0
12      25      50
13      0      0
14      0      0
15      0      0
16      0      0
17      243      729
18      0      0

but when I do it outside of the EXEC .. it sorts it correctly.

Vadim - thanks tried your suggestion but it still didn't work

Do you guys think it has something to do with the fact that I am using an EXEC to do this ??

Thanks



0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:azrakdragon
ID: 12256354
Guys I think I see the problem here - it is the join although yesterday I swear I got the correct figures even with the join. Anyway - why would this be a problem?
0
 
LVL 6

Accepted Solution

by:
vadim_ti earned 750 total points
ID: 12256542
may be the problem because INTO clause
SELECT ....
                  INTO #TEST
try this without into

0
 

Author Comment

by:azrakdragon
ID: 12256847
Hi Vadim,
you can use the identity column without the into so I have to use it
I actually worked around the problem by changing some of my logic but would still be interested if someone knows why the join is causing this issue
It must be something to do with the identity column and the join and how it assigns an identity when you join - remember this is a right join guys

Thanks
0
 
LVL 59

Assisted Solution

by:Julian Hansen
Julian Hansen earned 750 total points
ID: 12256874
Azrakdragon,

I am confused - what is wrong with the sorting in the above example - you have not given column headings and your original query has more than 3 columns.

Here are some ideas

The sort order is determined by the order by clause. I don't know what types your fields are but if they are non-numeric then the sort order will be determined by the sort order of the system. This does not explain however, why the exec produces different results from the other query.

What I would do is change your exec statement to


SELECT 'SELECT
                  u.USER
                  , IDENTITY(INT, 1, 1) AS '+''''+@Rank+''''+'
                  , ISNULL(tr.'+@Day_Name+'TOTALR, 0) AS '+''''+@TotalR+''''+'
                  ,ISNULL(tr'+@Day_Name+'TOTALP, 0) AS '+''''+@TotalP+''''+'
                  INTO '+ @TotalTable+'
                  FROM ##TOTAL '+@Day_Name+' tr
                  RIGHT JOIN #Temp_Users u ON tr.UserID = u.UserId
                  order by '+@TotalP+' DESC'

This will output the query that is actually being executed. Match that to the second query. Post the output as well - that will help
0
 

Author Comment

by:azrakdragon
ID: 12305388
Hi guys,
Well I worked around my problem, The problem was the join, so I took the join out and did it in some processing before I did the insert with the identity.
I think it had something to do with how SQL was trying to join, order by and then insert using identity all at once.
I have split points however for the help.
Thanks

Julian just for the record - this is how it was sorting when the join was in place
Rank(Identity)            TotalP
1                      0    
2                       2    
3                       0    
4                       1    
5                       3    
6                       0    

This is how it should be sorted or how it sorted after the join was taken out

Rank(Identity)            TotalP
1                      1    
2                       2    
3                       3    
4                       0    
5                       0    
6                       0    
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question