Group By clause under select query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Group By clause under select query

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2007-06-28 : 17:48:12

I want to use the group by clause for my select query, but i am calling a userdefined function for one column in it. but how to use that userdefined function column FieldTo, under group by clause.

SELECT RM.RMID, RM.rmtitle, RM.sequenceno, dbo.ccsFunctionRecipientTO(29,'RM') as FieldTo
FROM TAB_ccsNetRM AS RM JOIN TAB_ccsNetUserAccess AS U ON RM.ProgID = U.ProgID INNER JOIN
GROUP BY RM.RMID, RM.rmtitle, RM.sequenceno, FieldTo

When i try to execute the above one i get the following error message:
--------------------------------
Invalid column name 'FieldTo'.
--------------------------------

Here is my userdefined function:
------------------------------------------------------------------
ALTER FUNCTION [dbo].[ccsFunctionRecipientTO](@ModuleID integer, @ModuleName VARCHAR(10))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @s VARCHAR(8000)
SET @s=''
SELECT @s=@s + [Name] + char(13)
from TAB_ccsNetRecipients where ModuleRecordID = @ModuleID and ModuleName = @ModuleName and RecipientType = 'TO'
If @s IS NOT NULL AND @s <> ''
BEGIN
SELECT @s = left(@s, Datalength(@s)-1)
END
ELSE
BEGIN
SELECT @s = ''
END
Return @s
END

------------------------------------------------------------------

Please help, since i must have all fields under groupby clause for using it in reporting services. or else is there any other way to get the distinct records.
Thank you very much for the information.

mobius
Starting Member

13 Posts

Posted - 2007-06-28 : 17:54:01

You're using every field in your select in your group by. You don't need to include the FieldTo field in there. It's what you're 'grouping for' as it were.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-06-28 : 19:46:35

I just glanced over your code, but since the parameters for the function never change, why call it for every row?[CODE]DECLARE @FieldTo VARCHAR(8000)
SET @FieldTo = dbo.ccsFunctionRecipientTO(29,'RM')

SELECT RM.RMID, RM.rmtitle, RM.sequenceno, @FieldTo as FieldTo
FROM TAB_ccsNetRM AS RM JOIN TAB_ccsNetUserAccess AS U ON RM.ProgID = U.ProgID INNER JOIN
GROUP BY RM.RMID, RM.rmtitle, RM.sequenceno[/CODE]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources