Unable to Create Indexed View

Question

I am trying to put an index on a view but keep running into the issue where I get an error

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.

Code:

USE [DB]
GO

/****** Object:  View [dbo].[MonthlyView]    Script Date: 03/17/2014 15:19:10 ******/
SET ANSI_NULLS ON
GO

--Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
    QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO


SET QUOTED_IDENTIFIER ON
GO

ALTER VIEW [dbo].[MonthlyView2]
WITH SCHEMABINDING 
AS
SELECT                
                      ISNULL(SUM(I.RSQty),0) AS RSQty,
                      ISNULL(SUM(I.COGQty),0) AS COGQty,
                      ISNULL(SUM(I.DSQty),0) AS DSQty, 
                      ISNULL(SUM(I.LossQty),0) AS LossQty,
                      ISNULL(SUM(I.AbuseQty),0) AS AbuseQty,
                      ISNULL(SUM(I.InventoryChgQty),0) AS InventoryChgQty,
                      ISNULL(SUM(I.RSRev),0) AS RSRev,
                      ISNULL(SUM(I.COGRev),0) AS COGRev,
                      ISNULL(SUM(I.DSRev),0) AS DSRev,
                      ISNULL(SUM(I.LossRev),0) AS LossRev,
                      ISNULL( SUM(I.AbuseRev),0) AS AbuseRev,
                      ISNULL(SUM(I.InventoryChgRev),0) AS InventoryChgRev, 
                      ISNULL(SUM(I.EnergyChg + I.EnvironChg + I.HandlingChg + I.OtherChg + I.Discount + I.StopMin),0) AS Surcharge,
                      COUNT_BIG(*) as cbig,
                      M.MonthId, PeriodEnd, id, Sku
FROM         dbo.ItemRev AS I INNER JOIN
                      dbo.Weeks AS w ON I.WeekId = w.WeekId INNER JOIN
                      dbo.Month AS M ON M.MonthId = w.MonthId
GROUP BY  M.MonthId, PeriodEnd, id, Sku


GO

CREATE UNIQUE CLUSTERED INDEX IX_vMonthly on [dbo].[MonthlyView2]
(
    [ID] asc,
    [PeriodEnd] asc,
    [MonthId] asc,
    [SKU] asc
)

go

score 1 · Answer 1 · answered Oct 17 '16 at 20:54

I ran into this error, and found that it worked to use

sum(isnull(myfieldname, 0))

instead of

isnull(sum(myfieldname), 0)

Note that there's a subtle difference in the meaning of these, as the first will treat null values as 0, but the second will give a zero result if any are null.

score 0 · Answer 2 · answered Mar 17 '14 at 21:20

0

The docs list a ton of restrictions. What you're doing just is not allowed.

A simple fix would be to create the index on a view that does not perform the disallowed operation and wrap it in another view that applies the "scalar computation" (the ISNULL in your code).

answered Mar 17 '14 at 21:20

usr

168,620
35
240
369

When I had the SUM aggregate by itself it threw and error that it needed the ISNULL in there. Once I added that I got my current error. – user3430703 Mar 17 '14 at 22:56
@user3430703 did you read the error message? Why do you think that error might have been thrown? What's your interpretation of it? – usr Mar 17 '14 at 23:23
That the aggregate wanted me to use the ISNULL to make sure there are not any null values being aggregated. – user3430703 Mar 17 '14 at 23:56
I remove the ISNULL function so I am just summing the values and I get. because the view references an unknown value (SUM aggregate of nullable expression). Consider referencing only non-nullable values in SUM. ISNULL() may be useful for this. – user3430703 Mar 18 '14 at 00:02

Unable to Create Indexed View

2 Answers2