January 17, 2012 at 6:30 am
Hi All,
I have a stored proc which has 1 parameter which is a team id, when that is passed in it goes away and selects all users that are associated with that team, i need to select "Unallocated User" from another table but im having trouble setting this union out can some one help me? here is my stored proc
select 0 as IntUserID, 'Select...' as chrFullname union all
Select U.intUserID, U.chrUnAllocatedUser union all
Select wrp_sys_User.IntUserId, wrp_sys_User.chrForename + ' ' + wrp_sys_User.chrSurname as chrFullName from wrp_team inner join
wrp_TeamUser on wrp_Team.intTeamID = wrp_teamUser.intTeamID inner join
wrp_sys_User on wrp_teamUser.intUserID = wrp_sys_User.intUserID inner join
wrp_UnAllocatedUser as u on wrp_Team.intTeamID = u.intTeamID
where wrp_Team.intTeamID = 7021
order by intUserID asc
Its the second line from the top
i want to recieve results like this
intUserID | FullName
0 Select...
1 Pete Long
2 Mark Long
3 UnAllocated User
The date base im working with is a pile of CRAP so this is why i have to use a union.....
Any help will be highly appreciated
January 17, 2012 at 6:41 am
:blink: would you mind in posting your business requirements as well as the schema of the involved tables?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 17, 2012 at 6:42 am
DDL and sample data would be very useful, click this link to see how best to lay it out. Help us to help you![/url] 😀
Off the bat, your UNION statement is incorrect. If I lay it out like this, does it make it easier for you to see what is incorrect?
SELECT 0 AS IntUserID, 'Select...' AS chrFullname
UNION ALL
SELECT U.intUserID, U.chrUnAllocatedUser
UNION ALL
SELECT wrp_sys_User.IntUserId, wrp_sys_User.chrForename + ' ' + wrp_sys_User.chrSurname AS chrFullName
FROM wrp_team
INNER JOIN wrp_TeamUser ON wrp_Team.intTeamID = wrp_teamUser.intTeamID
INNER JOIN wrp_sys_User ON wrp_teamUser.intUserID = wrp_sys_User.intUserID
INNER JOIN wrp_UnAllocatedUser AS u ON wrp_Team.intTeamID = u.intTeamID
WHERE wrp_Team.intTeamID = 7021
ORDER BY intUserID ASC
Essentially, when you have multiple SELECT statements that you are unioning together it's like having separate batches. They can't reference each other. So your 2nd SELECT statement is missing a FROM.
January 17, 2012 at 6:43 am
From whatever I could understand, I have made some changes
Check if this is what you need
select 0 as IntUserID, 'Select...' as chrFullname
union all
Select U.intUserID, U.chrUnAllocatedUser
-- there should be some FROM clause, add the 3 lines below
from wrp_team
inner joinwrp_UnAllocatedUser as u on wrp_Team.intTeamID = u.intTeamID
where wrp_Team.intTeamID = 7021
union all
Select wrp_sys_User.IntUserId, wrp_sys_User.chrForename + ' ' + wrp_sys_User.chrSurname as chrFullName
from wrp_team
inner joinwrp_TeamUser on wrp_Team.intTeamID = wrp_teamUser.intTeamID
inner joinwrp_sys_User on wrp_teamUser.intUserID = wrp_sys_User.intUserID
inner joinwrp_UnAllocatedUser as u on wrp_Team.intTeamID = u.intTeamID -- i think this join is not needed
where wrp_Team.intTeamID = 7021
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
January 17, 2012 at 6:49 am
Or maybe...
WITH CTE AS (
Select wrp_sys_User.IntUserId, wrp_sys_User.chrForename + ' ' + wrp_sys_User.chrSurname as chrFullName, U.intUserID, U.chrUnAllocatedUser
from wrp_team inner join wrp_TeamUser on wrp_Team.intTeamID = wrp_teamUser.intTeamID
inner join wrp_sys_User on wrp_teamUser.intUserID = wrp_sys_User.intUserID
inner join wrp_UnAllocatedUser as u on wrp_Team.intTeamID = u.intTeamID
where wrp_Team.intTeamID = 7021
)
select 0 as IntUserID, 'Select...' as chrFullname union all
Select intUserID, chrUnAllocatedUser FROM CTE UNION ALL
SELECT IntUserID, chrFullName FROM CTE
order by intUserID asc
John
January 17, 2012 at 7:00 am
Im using this one
SELECT 0 AS IntUserID, 'Select...' AS chrFullname
UNION ALL
SELECT U.intUserID, U.chrUnAllocatedUser
from wrp_UnAllocatedUser as U
UNION ALL SELECT wrp_sys_User.IntUserId, wrp_sys_User.chrForename + ' ' + wrp_sys_User.chrSurname AS chrFullName
FROM wrp_team INNER JOIN
wrp_TeamUser ON wrp_Team.intTeamID = wrp_teamUser.intTeamID INNER JOIN
wrp_sys_User ON wrp_teamUser.intUserID = wrp_sys_User.intUserID INNER JOIN
wrp_UnAllocatedUser AS u ON wrp_Team.intTeamID = u.intTeamID WHERE wrp_Team.intTeamID = 7021
ORDER BY intUserID ASC
But im getting the following error
"Msg 457, Level 16, State 1, Line 1
Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict."
January 17, 2012 at 7:02 am
As Paul and Cadavre have already asked, post your table DDL and some sample data and it'll be a lot easier for us to help you.
John
January 17, 2012 at 7:04 am
.Netter (1/17/2012)
But im getting the following error"Msg 457, Level 16, State 1, Line 1
Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict."
Last go from me, DDL and sample data would be very useful, click this link to see how best to lay it out. The members of this forum are unpaid volunteers, so help us to help you! DDL and sample data will allow us to provide you with working, fully tested code. Without DDL and sample data, the best you can get is "best guesses".
Thanks[/url] 😉
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply