Cant create index on view

Hi,

SQL Server 2005 (SP2) Developer edition on XP Pro

Can anyone help with setting up an indexed view? Part of the select statement calls a couple of very simple functions which are deterministic. The functions which are shown below do not perform any aggregation.

This is the error message.

Msg 8668, Level 16, State 0, Line 2
Cannot create the clustered index 'IX3_SA' on view 'XPS.dbo.SA_INDEXED' because the select list of the view contains an expression on result of aggregate function or grouping column. Consider removing expression on result of aggregate function or grouping column from select list.

Function definitions:

create FUNCTION [dbo].[MonthName](@.MonthNo TINYINT)
RETURNS CHAR(3)
with schemabinding
AS
BEGIN
DECLARE @.MonthName CHAR(3)
IF @.MonthNo BETWEEN 1 AND 12 SET @.MonthName = SUBSTRING('JanFebMarAprMayJunJulAugSepOctNovDec',((@.MonthNo*3) -2),3)
RETURN ISNULL(@.MonthName,'')
END

create FUNCTION [dbo].[FinancialYear](
@.Year INT,
@.MonthNo TINYINT,
@.YearEndMonthNo TINYINT)
RETURNS INT
with schemabinding
AS
BEGIN
DECLARE @.FinancialYear INT
IF @.MonthNo <= @.YearEndMonthNo
SET @.FinancialYear = @.Year
ELSE
SET @.FinancialYear = @.Year + 1
RETURN @.FinancialYear
END

The first part of the select statement is as follows:

(SELECT
count_big(*) as CB,

dbo.FinancialYear(YR.Number,MTH.Number,YEAR_END_MONTH) AS REPORT_FINANCIAL_YEAR,
dbo.MonthName(MTH.Number) + ' ' + CAST(YR.Number AS CHAR(4)) AS REPORT_MONTH,
dbo.MonthName(YEAR_END_MONTH) + ' ' + CAST(dbo.FinancialYear(YR.Number,MTH.Number,YEAR_END_MONTH) AS CHAR(4)) AS REPORT_FINANCIAL_YEAR_END,

I can create the index on this view if I comment out the calls to the functions so I know that is where the problem is. I can't however see why this is erroring as both only return a single value without any agregation.

Anyone got any ideas on this?

Thanks in advance

David.

Well that met with a deafening silence.

The answer was to move these function calls out of the indexed view definition into a standard view that sits above it.