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
vinu512
vinu512
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3623 Visits: 1626
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 ;-)
Grass
Grass
SSC Veteran
SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)

Group: General Forum Members
Points: 215 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.
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)

Group: General Forum Members
Points: 93807 Visits: 38955
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.

Cool
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)
vinu512
vinu512
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3623 Visits: 1626
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 ;-)
Grass
Grass
SSC Veteran
SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)

Group: General Forum Members
Points: 215 Visits: 342
The code I posted in my first post is in SQL Exexute Task from a SSIS package.
vinu512
vinu512
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3623 Visits: 1626
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 ;-)
Grass
Grass
SSC Veteran
SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)

Group: General Forum Members
Points: 215 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.
vinu512
vinu512
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3623 Visits: 1626
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 ;-)
Grass
Grass
SSC Veteran
SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)

Group: General Forum Members
Points: 215 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.
vinu512
vinu512
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3623 Visits: 1626
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 ;-)
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