Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL Server
here is my procedure
ALTER PROCEDURE [dbo].[RmsUpdateRoAssignReAssign1]
(
@XMLAssignRo  AS XML
)
AS
BEGIN
	UPDATE RmsActivity 
				SET 
						RmsProcessorID = DocumentElement.AssignReAssignRo.value('(UserID)[1]', 'INT' ),
						LastUpdatedByID= DocumentElement.AssignReAssignRo.value('(UserID)[1]', 'INT' ),
						LastUpdatedDate=GETDATE()
						
				FROM	
						@XMLAssignRo.nodes('/DocumentElement/AssignReAssignRo') DocumentElement(AssignReAssignRo)
						INNER JOIN RmsUserSkillSet RS
						ON 
							 RS.RmsSkillSetTypeID =  
							 CASE 
								 WHEN DocumentElement.AssignReAssignRo.value('(Aging)[1]', 'INT' )< =20  THEN 1					
								 WHEN DocumentElement.AssignReAssignRo.value('(Aging)[1]', 'INT' ) BETWEEN 21 AND 30  THEN 2
								 WHEN DocumentElement.AssignReAssignRo.value('(Aging)[1]', 'INT' ) >=31 THEN 3							
							 END 
						INNER JOIN tblRoamingUsers RU
						ON RS.RoamingUserID=RU.RoamingUserID
						AND RU.UserID=DocumentElement.AssignReAssignRo.value('(UserID)[1]', 'INT' )
						INNER JOIN tblCampus C
						ON	C.CampusID=DocumentElement.AssignReAssignRo.value('(CampusID)[1]', 'INT' )
						AND RU.SchoolID=C.SchoolID
				WHERE
						RmsActivityID = DocumentElement.AssignReAssignRo.value('(ActivityID)[1]', 'INT' )
END		
 
i am getting the following error
(Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.)
 
'RoamingUserID' is unique for a particular user and campus
i m not able to get y i m getting the above error
 
[edit]Code block sorted out - OriginalGriff[/edit]
Posted 8-Oct-12 21:46pm
Edited 8-Oct-12 21:52pm
v2
Comments
digimanus at 12-Oct-12 11:16am
   
Have you done a select * from.....?
Then you shouls see the problem

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

You might have to check each join where it is giving more than one rows.
U can also use distinct clause where it returns more than one rows.
And rather applying as a case when, u should take as a inner join and then make join.
 
Regards,
Keep helping nature..!
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Mathew Soji 274
1 BillWoodruff 270
2 Afzaal Ahmad Zeeshan 244
3 Sergey Alexandrovich Kryukov 240
4 Raul Iloc 160
0 OriginalGriff 6,219
1 Sergey Alexandrovich Kryukov 5,853
2 DamithSL 5,103
3 Manas Bhardwaj 4,549
4 Maciej Los 3,845


Advertise | Privacy | Mobile
Web01 | 2.8.1411019.1 | Last Updated 18 Oct 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100