I am trying to create a simple indexed view on the query below. But when I try to create a unique clustered index on it, I get the following error:
Cannot create the clustered index '..' on view '..' 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.
The query I used is as follows:
SELECT
[Manufacturer]
,ISNULL(SUM([QAV]),0) as AvgQAV
,ISNULL(SUM([BackOrders$]),0)as AvgBackorder$
,DATEPART(year,[Date])as Year
,DATEPART(month,[Date])as Month
,[fixSBU]
,[DC Name]
FROM [dbo].[TABLE1]
Group By
[Manufacturer]
,DATEPART(year,[Date])
,DATEPART(month,[Date])
,[fixSBU]
,[DC Name]
Could anyone tell me the possible cause for this?
As you can see I am already using the ISNULL
function.
Here is a link to all the restrictions of an index view: https://msdn.microsoft.com/en-us/library/ms191432.aspx#Restrictions
From the documentation these two items should stick out:
Also, you need to change your ISNULL statements. Right now you have ISNULL(SUM([BackOrders$]),0)
and it should be SUM(ISNULL([BackOrders$], 0))
. You need to SUM the ISNULL, not the other way around.