August 16, 2005 at 7:25 am
I am trying to create a stored procedure using the following TSQL:
SELECT DISTINCT
RTRIM(project.pname),
SUBSTRING(time_user_attribs.string_value,3,99),
SUBSTRING(time.id_user,3,99),
CASE WHEN RTRIM(task_attribs.string_value) = 'XX' THEN
''
ELSE RTRIM(task_attribs.string_value)
END,
CONVERT(varchar(8), DATEADD(d,7 - DATEPART(dw, time.record_date), time.record_date), 112),
SUM(time.time_amount*100)
FROM .......etc
GROUP BY RTRIM(project.pname),
SUBSTRING(time_user_attribs.string_value,3,99),
SUBSTRING(time.id_user,3,99),
RTRIM(task_attribs.string_value),
CONVERT(varchar(8), DATEADD(d,7 - DATEPART(dw, time.record_date), time.record_date), 112)
but I keep getting the following message:
Column 'task_attribs.string_value' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Can anyone explain why this is happening?
Thanks
August 16, 2005 at 7:35 am
Because you are grouping by
GROUP BY ...
RTRIM(task_attribs.string_value),
not task_attribs.string_value that you use in your select clause
Vasc
August 16, 2005 at 7:37 am
Thanks Vasc but I don't understand. Is the RTRIM causing the problem?
August 16, 2005 at 7:41 am
yes
rtirm(task_attribs.string_value)!=task_attribs.string_value
you can replace task_attribs.string_value with
rtrim(task_attribs.string_value) in your select and will work
Vasc
August 16, 2005 at 7:49 am
My Select statement already contains rtrim(task_attribs.string_value) in the Case statement.
August 16, 2005 at 8:01 am
Seems that the problem is caused by the CASE
statement.
Vasc
August 16, 2005 at 8:10 am
one work arround can be
select case when task_attribs.string_value='XX'+replicate(' ',len(task_attribs.string_value)) then ''
else task_attribs.string_value end
group by task_attribs.string_value
Vasc
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply