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 Thursday, April 12, 2012 10:33 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 8:48 AM
Points: 1,118, Visits: 1,582
I think your DDL and sample data script is not correct. Its returning errors. I think you should check it.

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 #1282884
Posted Thursday, April 12, 2012 11:27 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
vinu:

I tried using select 1 and it still gives me error.but i haven't try distinct yet.if i use distinct what fields do i use from the table? I mean what the statement would be (select distinct (which column names comes here) from table name.

Also, you mentioned that my ddl and data is not right. the create table script is right and the data i got from those table i created. the data that are in the select statements are shown in the table but it is not all the data. there are alot of rows so i just got few of it. please let me know what can be done.

I forgot to mention that the error occurred in the SSIS package 'SQL execute task'. that script is in that task. when the package run, is comes to that task and fail with the error.

thanks.
Post #1282894
Posted Thursday, April 12, 2012 11:34 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:25 PM
Points: 23,044, Visits: 31,568
Grass (4/12/2012)
vinu:

I tried using select 1 and it still gives me error.but i haven't try distinct yet.if i use distinct what fields do i use from the table? I mean what the statement would be (select distinct (which column names comes here) from table name.

Also, you mentioned that my ddl and data is not right. the create table script is right and the data i got from those table i created. the data that are in the select statements are shown in the table but it is not all the data. there are alot of rows so i just got few of it. please let me know what can be done.

thanks.


Best thing you could do is create an empty database, call it sandbox or playpen, copy and paste your code from the forum to a query window and run it in the newly created empty database. If anything fails, fix it, drop everything from the database and rerun the script again. Repeat this process until your scripts run without errors. Then repost the corrected scripts.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1282900
Posted Friday, April 13, 2012 2:34 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 8:48 AM
Points: 1,118, Visits: 1,582
Grass, you need to do a few things right so that we know what the problem is and can head into the right direction.

1. Do what Lynn said in his last reply.
2. What does the Execute Process task do??....What task is it executing?
3. Can you tell us clearly which part is causing the error?....The code you posted or the SSIS package?

We won't be able to move forward without the DDL and the sample data.


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 #1282964
Posted Friday, April 13, 2012 7:25 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
The code I posted in my first post is in SQL Exexute Task from a SSIS package.
Post #1283125
Posted Friday, April 13, 2012 10:44 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 8:48 AM
Points: 1,118, Visits: 1,582
Grass (4/13/2012)
The code I posted in my first post is in SQL Exexute Task from a SSIS package.


Fine, I got the code. Now, please post the correct DDL and some readily usable sample data against which I can check what is wrong with your code.
Do what Lynn said in his last post. As soon as it is done we can start looking for what's wrong with the code.


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 #1283575
Posted Saturday, April 14, 2012 2:18 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
I think I actually found out which part of the code is giving the error. It is on the first two SET statements when I pass the three variables @Project_Id, @Matter_Id, and Project_Desc.Do you think I can rewrite that section of SET statements then it might work?

Thanks.
Post #1283676
Posted Sunday, April 15, 2012 10:08 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 8:48 AM
Points: 1,118, Visits: 1,582
Grass (4/14/2012)
I think I actually found out which part of the code is giving the error. It is on the first two SET statements when I pass the three variables @Project_Id, @Matter_Id, and Project_Desc.Do you think I can rewrite that section of SET statements then it might work?

Thanks.


Yes. Try using Select Distinct or Select 1 in those SET statements.


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 #1283868
Posted Sunday, April 15, 2012 10:27 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
I tried using before but didn't work.can u tell me how to use the distinct to replace SET statement.what fields needs to be distinct?

thanks.
Post #1283871
Posted Monday, April 16, 2012 5:14 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 8:48 AM
Points: 1,118, Visits: 1,582
Grass (4/15/2012)
I tried using before but didn't work.can u tell me how to use the distinct to replace SET statement.what fields needs to be distinct?

thanks.

Try these for Distinct:

SET @Project_ID = (SELECT Distinct project_id FROM int_aux_project WHERE [/img]project_cd = @Matter_ID)

SET @Project_Desc = (SELECT Distinct project_nm FROM int_aux_project WHERE project_cd = @Matter_ID)

SET @Listing_ID = (SELECT Distinct listing_id FROM int_aux_lst_custom WHERE string_value = @Billing_ID AND LST_CUSTOM_DEF_ID = '-10017')

SET @User_ID= (SELECT Distinct user_id FROM int_user WHERE listing_id = @Listing_ID)

If Distinct doesn't work, try these for Select 1:

SET @Project_ID = (SELECT Top 1 project_id FROM int_aux_project WHERE [/img]project_cd = @Matter_ID)

SET @Project_Desc = (SELECT Top 1 project_nm FROM int_aux_project WHERE project_cd = @Matter_ID)

SET @Listing_ID = (SELECT Top 1 listing_id FROM int_aux_lst_custom WHERE string_value = @Billing_ID AND LST_CUSTOM_DEF_ID = '-10017')

SET @User_ID= (SELECT Top 1 user_id FROM int_user WHERE listing_id = @Listing_ID)


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 #1284005
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse