azrakdragon
asked on
Identity issue again
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+'TOT ALR, 0) AS '+''''+@TotalR+''''+'
,ISNULL(tr'+@Day_Name+'TOT ALP, 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??
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+'TOT
,ISNULL(tr'+@Day_Name+'TOT
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??
When you say it does not return the correct results in the IDENTITY column - can you be a bit more specific.
try this way
EXEC('SELECT
u.USER
, IDENTITY(INT, 1, 1) AS '+''''+@Rank+''''+'
, ISNULL(tr.'+@Day_Name+'TOT ALR, 0) AS '+''''+@TotalR+''''+'
,ISNULL(tr'+@Day_Name+'TOT ALP, 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+'TOTA LP, 0) DESC')
EXEC('SELECT
u.USER
, IDENTITY(INT, 1, 1) AS '+''''+@Rank+''''+'
, ISNULL(tr.'+@Day_Name+'TOT
,ISNULL(tr'+@Day_Name+'TOT
INTO '+ @TotalTable+'
FROM ##TOTAL '+@Day_Name+' tr
RIGHT JOIN #Temp_Users u ON tr.UserID = u.UserId
order by ISNULL(tr'+@Day_Name+'TOTA
ASKER
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
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
ASKER
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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