Click here to Skip to main content
Rate this: bad
Please Sign up or sign in to vote.
See more: SQL Server
here is my procedure
ALTER PROCEDURE [dbo].[RmsUpdateRoAssignReAssign1]
@XMLAssignRo  AS XML
	UPDATE RmsActivity 
						RmsProcessorID = DocumentElement.AssignReAssignRo.value('(UserID)[1]', 'INT' ),
						LastUpdatedByID= DocumentElement.AssignReAssignRo.value('(UserID)[1]', 'INT' ),
						@XMLAssignRo.nodes('/DocumentElement/AssignReAssignRo') DocumentElement(AssignReAssignRo)
						INNER JOIN RmsUserSkillSet RS
							 RS.RmsSkillSetTypeID =  
								 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							
						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
						RmsActivityID = DocumentElement.AssignReAssignRo.value('(ActivityID)[1]', 'INT' )
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
digimanus at 12-Oct-12 11:16am
Have you done a select * from.....?
Then you shouls see the problem

1 solution

Rate this: bad
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.
Keep helping nature..!

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 555
1 Kornfeld Eliyahu Peter 409
2 Maciej Los 369
3 DamithSL 196
4 OriginalGriff 188
0 OriginalGriff 6,353
1 DamithSL 4,854
2 Maciej Los 4,466
3 Kornfeld Eliyahu Peter 4,058
4 Sergey Alexandrovich Kryukov 3,897

Advertise | Privacy | Mobile
Web01 | 2.8.141220.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