Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Need Help with the Error 'Subquery returned more than 1 value'. Expand / Collapse
Author
Message
Posted Wednesday, April 11, 2012 4:57 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:59 AM
Points: 45, Visits: 342
[quote][/quote]
Post #1282061
Posted Wednesday, April 11, 2012 5:04 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 19, 2014 5:16 AM
Points: 887, Visits: 1,774
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.
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

Jeremy Oursler
Post #1282063
Posted Wednesday, April 11, 2012 5:24 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:59 AM
Points: 45, Visits: 342
[center][/center]
Post #1282066
Posted Wednesday, April 11, 2012 5:25 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:59 AM
Points: 45, Visits: 342
[quote][/quote]
Post #1282067
Posted Wednesday, April 11, 2012 5:29 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 19, 2014 5:16 AM
Points: 887, Visits: 1,774
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

Jeremy Oursler
Post #1282068
Posted Wednesday, April 11, 2012 5:42 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:59 AM
Points: 45, Visits: 342

Post #1282072
Posted Thursday, April 12, 2012 1:13 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:13 AM
Points: 1,127, Visits: 1,594
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
Post #1282172
Posted Thursday, April 12, 2012 7:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:59 AM
Points: 45, 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.
Post #1282379
Posted Thursday, April 12, 2012 8:13 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 19, 2014 5:16 AM
Points: 887, Visits: 1,774
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

Jeremy Oursler
Post #1282406
Posted Thursday, April 12, 2012 8:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:59 AM
Points: 45, 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
Post #1282414
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse