April 12, 2012 at 10:33 pm
April 12, 2012 at 11:27 pm
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.
April 12, 2012 at 11:34 pm
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.
April 13, 2012 at 2:34 am
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.
April 13, 2012 at 7:25 am
The code I posted in my first post is in SQL Exexute Task from a SSIS package.
April 13, 2012 at 10:44 pm
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.
April 14, 2012 at 2:18 pm
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.
April 15, 2012 at 10:08 pm
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.
April 15, 2012 at 10:27 pm
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.
April 16, 2012 at 5:14 am
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)
April 16, 2012 at 7:35 am
Distinct does not work but Top 1 works. I only change TOP 1 in the first 2 SET statements and it works, third and fourth statements i didn't change anything.
thanks.
June 12, 2013 at 4:07 am
can anyone help me? i am new to db....
CREATE proc [dbo].[student_marks]
@roll_no varchar(20)
AS
BEGIN
CREATE TABLE #temp
(
semester INT,
marks INT
)
INSERT INTO #temp
(
semester,
marks
)
SELECT
semester,
( SELECT SUM ( marks )/count(noof_sub)
FROM student_details sd
INNER JOIN student_marks sm ON sd.roll_no = sm.roll_no
WHERE sd.roll_no = @roll_no
AND ri.semester = sm.sem_attended
group by semester
) marks
FROM student_details sd
INNER JOIN student_marks sm ON sd.roll_no = sm.roll_no
WHERE sm.roll_no=@roll_no
group by semester
SELECT *FROM #temp
end
the problem is when i execute the query it returns "subquery returned more than 1 value' error".because of that subquery contains 3 semesters and marks.when i remove the group by function in subquery it returns the same value for 3 rows.
but i need the result like semester marks
1 82
2 75
3 60
what should i do to overcome this problem????thanks in advance :-):-)
June 12, 2013 at 4:37 am
As Grass was told - we really need to see the sample data you are running this query against in order to make any meaningful suggestions.
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
June 12, 2013 at 4:43 am
i don't understand,what you mean by sample data???
June 12, 2013 at 7:16 am
nitha jen (6/12/2013)
i don't understand,what you mean by sample data???
Sample data as outlined by reading the article found at the first link in my signature. Additionally you really should start your own thread for your question instead of jumping into somebody else's.
_______________________________________________________________
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 - 31 through 45 (of 45 total)
You must be logged in to reply to this topic. Login to reply