February 22, 2008 at 8:54 am
can anybody help me? I have a stored proc that searches our help desk system. One of the columns in the select statement is a text data type (c.comments). The problem is I can't use a DISTINCT or group by without generating the error message below. I do not want to return duplicate records in the result set.
Column 'c.Comments' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Here is the query:
Select
c.HDFirstLevelCallID,
tty.ShortDescr UserType,
Comments,
c.ReportedDate,
c.HDStaffId
From AsapAdmin..HDFirstLevelCall as c
Inner Join AsapAdmin..HDFirstLevelCheckedIssues i
on c.HDFirstLevelCallID = i.HDFirstLevelCallID
Inner Join AsapAdmin..lkpHDTrackingUserType tty
on c.HDTrackingUserTypeId = tty.HDTrackingUserTypeId
Inner Join #tempIssues ti
on ti.HDFirstLevelCallID = c.HDFirstLevelCallID
Left Join Users as u
on c.HDStaffId = u.UserId
Where c.HDFirstLevelCallID like IsNull((Cast(@CallNum As varchar)) + '%', c.HDFirstLevelCallID)
and c.HDTrackingHeadingId = ISNULL(@TrackingAreaId, c.HDTrackingHeadingId)
and c.HDTrackingUserTypeId = ISNULL(@TrackingTypeID, c.HDTrackingUserTypeId)
and c.HDFirstLevelCallID = isnull(ti.HDFirstLevelCallID, c.HDFirstLevelCallID)
and c.ReportedDate between isnull(@DateStart, (Select MIN(ReportedDate) - ' 23:59:59.998'
From AsapAdmin..HDFirstLevelCall)) and
isnull(@DateEnd + ' 23:59:59.998' , (Select MAX(ReportedDate) + ' 23:59:59.998'
From AsapAdmin..HDFirstLevelCall))
and (@TextSearch is NULL or Contains (c.Comments, @TextSearch ))
--Group By c.HDFirstLevelCallID,tty.ShortDescr,Comments,c.ReportedDate,c.HDStaffId
Order By c.HDFirstLevelCallID Desc
The group by is commented out because it will throw an error if it isn't. Thanks
February 25, 2008 at 9:37 am
well, text all the other blob typea are not allowed, but you can cast/convert to a varchar(8000) in order to work around it: this is typically what i would do:
Select
c.HDFirstLevelCallID,
tty.ShortDescr UserType,
CONVERT(VARCHAR(8000),Comments) AS COMMENTS,
c.ReportedDate,
c.HDStaffId
From AsapAdmin..HDFirstLevelCall as c
Inner Join AsapAdmin..HDFirstLevelCheckedIssues i
on c.HDFirstLevelCallID = i.HDFirstLevelCallID
Inner Join AsapAdmin..lkpHDTrackingUserType tty
on c.HDTrackingUserTypeId = tty.HDTrackingUserTypeId
Inner Join #tempIssues ti
on ti.HDFirstLevelCallID = c.HDFirstLevelCallID
Left Join Users as u
on c.HDStaffId = u.UserId
Where c.HDFirstLevelCallID like IsNull((Cast(@CallNum As varchar)) + '%', c.HDFirstLevelCallID)
and c.HDTrackingHeadingId = ISNULL(@TrackingAreaId, c.HDTrackingHeadingId)
and c.HDTrackingUserTypeId = ISNULL(@TrackingTypeID, c.HDTrackingUserTypeId)
and c.HDFirstLevelCallID = isnull(ti.HDFirstLevelCallID, c.HDFirstLevelCallID)
and c.ReportedDate between isnull(@DateStart, (Select MIN(ReportedDate) - ' 23:59:59.998'
From AsapAdmin..HDFirstLevelCall)) and
isnull(@DateEnd + ' 23:59:59.998' , (Select MAX(ReportedDate) + ' 23:59:59.998'
From AsapAdmin..HDFirstLevelCall))
and (@TextSearch is NULL or Contains (c.Comments, @TextSearch ))
Group By c.HDFirstLevelCallID,tty.ShortDescr,CONVERT(VARCHAR(8000),Comments) ,c.ReportedDate,c.HDStaffId
Order By c.HDFirstLevelCallID Desc
Lowell
February 26, 2008 at 9:12 am
Go easy with varchar(8000).
Especially when you are selecting other columns in addition to the varchar.
SQL Server 2000 refuses to retrieve a row when its size is greater than 8,000 (the total length of all fields being retrieved). Not being this deep in SQL Server, I surmize this has something to do with exceeding a "page" size.
SELECT DISTINCT text_column is not allowed. So is GROUP BY text_column.
February 26, 2008 at 9:18 am
I coded around the limitation by breaking the select into two statements, basically doing a select distinct id into a temp table and then doing another select based on the ids in the temp table, joining back to the base table. thanks for the help
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply