April 11, 2012 at 4:57 pm
April 11, 2012 at 5:04 pm
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 performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 11, 2012 at 5:24 pm
April 11, 2012 at 5:25 pm
April 11, 2012 at 5:29 pm
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 performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 11, 2012 at 5:42 pm
April 12, 2012 at 1:13 am
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
April 12, 2012 at 7:52 am
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.
April 12, 2012 at 8:13 am
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 performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 12, 2012 at 8:24 am
Sorry,
Don't understand what is DDL means and what data you need to see, the table that used in the select statement.
thanks
April 12, 2012 at 8:34 am
suhailtajraja (4/12/2012)
Sorry,Don't understand what is DDL means and what data you need to see, the table that used in the select statement.
thanks
DDL - Data Defintion Language. In this case it means the CREATE TABLE statements used to create the tables.
Data - Sample data for the table(s) that you provide the DDL for. The sample data needs to be provided in a readily consumable format, a series of INSERT INTO statements usually works best.
April 12, 2012 at 8:47 am
I can pretty much guarantee there is no need for a cursor for this. This is nothing more than a delete statement inside a loop. This can pretty easily be converted to a single delete statement. Once you post the ddl and some sample data we can knock this out pretty quick.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 12, 2012 at 9:00 am
this is just a shot in the dark but I assume you passing in @Matter_ID and @Project_ID?
See if this select would get all int_aux_favorites that you want deleted for a given matter_ID and Project_ID.
select iaf.*
from int_maslon_matter_recent immr
join int_aux_project iap on iap.project_cd = immr.MatterNumber
join int_aux_lst_custom ialc on immr.TimeKeeperNumber = ialc.string_value and LST_CUSTOM_DEF_ID = '-10017'
join int_user iu on iu.listing_id = ialc.listing_id
join int_aux_favorites iaf on iaf.user_id = iu.user_id and iaf.entity_id = iap.project_id
where immr.entity_id = @Project_ID
and immr.MatterNumber = @Matter_ID
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 12, 2012 at 9:14 am
Thanks for the reply. I will try this. Which part of the code needs to be replaced for this the code you provided.
April 12, 2012 at 9:35 am
There are far more tables than just the favorites. Also, we need sample data for each table.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 45 total)
You must be logged in to reply to this topic. Login to reply