SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need Help with the Error 'Subquery returned more than 1 value'.


Need Help with the Error 'Subquery returned more than 1 value'.

Author
Message
Grass
Grass
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 342
[quote][/quote]
CapnHector
CapnHector
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1369 Visits: 1789
suhailtajraja (4/11/2012)
[Execute SQL Task] Error: Executing the query "/* removes Matters from timekeepers frequently use..." failed with 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.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.


I am getting the above error and can't able to figure out where is the problem. Here is the chunk of the code:

/* removes Matters from timekeepers frequently used matters page */

DECLARE @Matter_ID varchar(8)
DECLARE @Project_ID int
DECLARE @Project_Desc varchar(150)
DECLARE @User_ID int
DECLARE @Listing_ID int
DECLARE @Billing_ID varchar(4)

/* declare a cursor from the table holding the matters added on the previous day */
DECLARE Matters_To_Add CURSOR FOR SELECT TimeKeeperNumber, MatterNumber FROM int_maslon_matter_recent

OPEN Matters_To_Add

FETCH NEXT FROM Matters_To_Add INTO @Billing_ID, @Matter_ID

WHILE @@FETCH_STATUS = 0

BEGIN

/* Find the Project_ID and the Description from the Matter Number */
SET @Project_ID = (SELECT project_id FROM int_aux_project WHERE project_cd = @Matter_ID)
SET @Project_Desc = (SELECT project_nm FROM int_aux_project WHERE project_cd = @Matter_ID)

/* Find the InterAction User_ID from the Billing ID, going through the int_aux_lst_custom table */
SET @Listing_ID = (SELECT listing_id FROM int_aux_lst_custom WHERE string_value = @Billing_ID AND LST_CUSTOM_DEF_ID = '-10017')
SET @User_ID= (SELECT user_id FROM int_user WHERE listing_id = @Listing_ID)

/* Only remove items from the int_aux_favorites table - this is consistent with InterAction's native behavior */

/* Verify that the project already exists in the user's Favorites menu */
IF EXISTS (SELECT * FROM int_aux_favorites where user_id = @User_ID and entity_id = @Project_ID)
BEGIN

/* Remove from the Favorites table */

DELETE FROM int_aux_favorites WHERE user_id = @User_ID and entity_id = @Project_ID

END

/* Loop to the next record in the source Cursor */
FETCH NEXT FROM Matters_To_Add INTO @Billing_ID, @Matter_ID

END

CLOSE Matters_To_Add

DEALLOCATE Matters_To_Add
-------------------------------------------------------------------------------------
How should rewrite this if this is a problem. Crazy
Can't able to tackle this issue. Need Help....

Thanks.


you need to figure out which query is returning multiple values. it will most likely be this one


SELECT * FROM int_aux_favorites where user_id = @User_ID and entity_id = @Project_ID



you can try:

SELECT 1 FROM int_aux_favorites where user_id = @User_ID and entity_id = @Project_ID



We can also try to eliminate the cursor to make every thing faster if you can please post DDL (Create table statements) and Sample data. if you need help on the format for the DDL and sample data please see the link in my signature.


For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2
Grass
Grass
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 342
[center][/center]
Grass
Grass
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 342
[quote][/quote]
CapnHector
CapnHector
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1369 Visits: 1789
the select 1 just returns 1 instead of the multiple values for your exist(). and for the cursor, we need the table deffinitions and sample data before we can say one way or the other if you can remove the cursor.


For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2
Grass
Grass
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 342


vinu512
vinu512
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1707 Visits: 1625
You should check your table for duplicates because one of your sub queries is returning multiple records from the table based on your parameters. This could happen if you have duplicate records.

Try using Select Distinct OR Select 1 to avoid the error.

Vinu Vijayan

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden ;-)
Grass
Grass
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 342
Hi Vinu:

I checked entity_id,and user_id are retruning multiple records in a table. So, what field do I need in my statement as a distinct.Are these entity_id
,user_id ones? or something else.

Thanks.
CapnHector
CapnHector
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1369 Visits: 1789
suhailtajraja (4/12/2012)
Hi Vinu:

I checked entity_id,and user_id are retruning multiple records in a table. So, what field do I need in my statement as a distinct.Are these entity_id
,user_id ones? or something else.

Thanks.


can you post DDL and sample data. that will really help us out in figuring out what will cut the multiple rows. also there may be a way to get rid of the cursor which we will see once you post sample data and DDL statements.


For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2
Grass
Grass
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 342
Sorry,

Don't understand what is DDL means and what data you need to see, the table that used in the select statement.

thanks
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search