CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2005indexed-view
itdeveloper
itdeveloper

Reputation: 339

Error creating indexed view

I have problem with a counting column in my view.

SELECT ColumnC, ColumnA % ColumnB AS ModuloColAColB, COUNT_BIG(*) AS cBig FROM dbo.T1
GROUP BY ColumnC, ModuloColAColB

Query is similar to this MSDN example: http://msdn.microsoft.com/en-us/library/ms191432.aspx

When I try to compile view I received error message like: "invalid column name ModuloColAColB"

So I change group by column names: 

SELECT ColumnC, ColumnA % ColumnB AS ModuloColAColB,
COUNT_BIG(*) AS cBig FROM dbo.T1
GROUP BY ColumnC, ColumnA, ColumnB

View is compiling correctly but when I try to add index I receive the error: 

"Cannot create the clustered index 'px_test' on view 'l' 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"

When I remove " ColumnA % ColumnB AS ModuloColAColB" all works fine.

Database version: SQL server 2005 Enterprise Edition.

Upvotes: 1

Views: 856

Answers (1)

gbn
gbn

Reputation: 432200

Try GROUP BY ColumnC, ColumnA % ColumnB ?

From your link:

..cannot contain... An expression on a column used in the GROUP BY clause, or an expression on the results of an aggregate.

I think this means you can't modulo on your group by columns. However, you should be able to do the expression in the GROUP BY and simply repeat in the select clause

Upvotes: 1

Related Questions