Case statement and group by clause in function

[sam hank]

Hi,
in this function, only using @c to decide whether to sum over the [ddd] column. i am doing it in this way but i am getting error.

CREATE FUNCTION abc 
(	
@Date_a datetime = '01/01/2010',
@Date_b datetime = '01/01/2012,
@c bit   = 0
)
RETURNS 
@ret TABLE 
(
[aaa]	[VARCHAR](100),
[bbb]   [DATETIME],
[ccc]   [DATETIME]
[ddd]   [VARCHAR](80),
[ID]	[INT] IDENTITY(1,1)
PRIMARY KEY CLUSTERED (
	[bbb],
	[ID]
)
)
AS BEGIN
INSERT into @ret (
		[aaa],
		[bbb],
		[ccc],
		[ddd]
	)
SELECT
	'abc',
	[bbb],
	[ccc],
	[ddd] = CASE @c WHEN 1 THEN [ddd] ELSE '' END
		FROM xyz(@Date_a, @Date_b, null, null)
		GROUP BY [aaa], [bbb],[ccc]
		CASE @c WHEN 1 THEN [ddd] ELSE '' END
RETURN	

END

[Rabbit]

Remove the [ddd] = part, that's only used to set variables and you're not trying to set a variable. You're also missing a comma in your group by clause after your [ccc] field.

[NeoPa]

Your SQL is nicely laid out (readable) Sam, but you didn't include the error message. Try Rabbit's suggestions, and if they don't work, consider coming back with a post that includes the error message.

[mohit2k8]

I think you have made some syntactical errors, those i have removed in given below code.

Create FUNCTION abc 
(    
@Date_a datetime = '01/01/2010',
@Date_b datetime = '01/01/2012',
@c bit = 0
)
RETURNS 
@ret TABLE 
(
[aaa]   [VARCHAR](100),
[bbb]   [DATETIME],
[ccc]   [DATETIME],
[ddd]   [VARCHAR](80),
[ID]    [INT] IDENTITY(1,1)
PRIMARY KEY CLUSTERED (
    [bbb],
    [ID]
)
)
AS BEGIN
INSERT into @ret (
        [aaa],
        [bbb],
        [ccc],
        [ddd]
    )
SELECT
    'abc',
    [bbb],
    [ccc],
    [ddd] = CASE @c WHEN 1 THEN [ddd] ELSE '' END
        FROM xyz(@Date_a, @Date_b, null, null)
        GROUP BY [aaa], [bbb],[ccc],
        CASE @c WHEN 1 THEN [ddd] ELSE '' END
RETURN    
 
END

[sam hank]

i have removed the [ddd]= part from the select, still it is showing error.
-----
Error Message: Msg 8120, Level 16, State 1, Procedure abc, Line 69
Column 'abc.ddd' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
------
also the case in group by clause, will it consider the '' after the "[ccc]," if the value is other than 1 ?

 GROUP BY [aaa], [bbb],[ccc], 
 CASE @c WHEN 1 THEN [ddd] ELSE '' END

[NeoPa]

That error message doesn't match the SQL we've seen, with or without the changes you've described.

Try this :

SELECT   'abc'
       , [bbb]
       , [ccc]
       , CASE @c WHEN 1 THEN [ddd] ELSE '' END AS [New_ddd]
FROM     xyz(@Date_a, @Date_b, null, null)
GROUP BY [aaa]
       , [bbb]
       , [ccc]
       , CASE @c WHEN 1 THEN [ddd] ELSE '' END

You may even get away with a last line of the following as it's T-SQL :

       , [New_ddd]

[mohit2k8]

In the above code, it may be a case after [ccc] it will get '' when value of @c is something else rather than 1.