Wednesday, October 17, 2012

Consider referencing only non-nullable values in SUM. ISNULL() may be useful for this


Error:
Msg 8662, Level 16, State 0, Line 1
Cannot create the clustered index "SumSales_Summary_TestIndex" on view "AdventureWorks.dbo.vSumSales_Summary_TestByProductLine" 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.

-- Table design
CREATE TABLE [dbo].[Sales_Summary_Test](
 [SaleID] [int] IDENTITY(1,1) NOT NULL,
 [OrderDate] [datetime] NULL,
 [ProductLine] [nvarchar](2) NULL,
 
 [SubTotal] [numeric](38, 6) NULL) ON [PRIMARY]
GO


-- Creating  a View
CREATE VIEW dbo.vSumSales_Summary_TestByProductLine
     WITH SCHEMABINDING 
AS
SELECT s.ProductLine AS ProductLine,
 COUNT_BIG(*) AS NumberOfProductLine, 
 SUM(s.SubTotal) AS SubTotal
FROM [dbo].[Sales_Summary_Test] AS s

WHERE ProductLine IS NOT NULL
GROUP BY s.ProductLine
go


SELECT * FROM vSumSales_Summary_TestByProductLine
go

-- Create a Index for view
CREATE UNIQUE CLUSTERED INDEX SumSales_Summary_TestIndex
 ON dbo.vSumSales_Summary_TestByProductLine(ProductLine)
go


Msg 8662, Level 16, State 0, Line 1
Cannot create the clustered index "SumSales_Summary_TestIndex" on view "AdventureWorks.dbo.vSumSales_Summary_TestByProductLine" 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.


Solution:
You must change NULL column to NOT NULL column
CREATE TABLE [dbo].[Sales_Summary_Test](
 [SaleID] [int] IDENTITY(1,1) NOT NULL,
 [OrderDate]   [datetime] NOT NULL,
 [ProductLine] [nvarchar](2) NOT NULL,
 [SubTotal]    [numeric](38, 6) NOT NULL
) ON [PRIMARY]

GO