February 18, 2012 at 4:21 am
[p]I am creating Indexed views using SQL Server 2008R2. When I create index on the View it give me following error.
Cannot create the clustered index 'v_CR_WriteOffReceiptWise_Rpt_Ind' on view 'PRSP_Live_010909_190112.dbo.v_CR_WriteOffReceiptWise_Rpt' 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.[/p]
My code is given Below
Create view v_CR_WriteOffReceiptWise_Rpt with schemabinding AS
SELECT
i.LEVEL3_CODE
,i.LEVEL3_NAME
,i.LEVEL4_CODE
,i.LEVEL4_NAME
,i.LEVEL5_CODE
,i.LEVEL5_NAME
,i.LEVEL6_CODE
,i.LEVEL6_NAME
,LL.member_code
,LL.loan_apply_date
,L.CHEQUE_DATE
,LL.DUE_DATE
,LL.RECEIPT_DATE
,L.PAYOFF_DATE
,LL.inst_no
,sum(isnull(LL.recpa,0)) RecPA
,sum(isnull(LL.recsc,0)) RecSc
,sum(isnull(LL.reclsc,0)) RecLSc
,sum(isnull(LL.rebate,0)) Rebate
,sum(isnull(LL.RecPenalty,0)) as RecPenalty
,L.PACKAGE_CODE
,pck.PACKAGE_NAME
,L.PRODUCT_CODE
,product.PRODUCT_NAME
,sum(isnull(LL.ADV_RECOVERY,0)) ADV_RECOVERY
,EXCESS
,L.Borrower_NO
,L.SANCTION_NO
,co.CO_CODE
,co.CO_NAME
,co.CO_TYPE
,co.Office_Code
,m.GENDER
,isnull(M.MEMBER_FIRST_NAME,'') + ' ' + isnull(M.MEMBER_Last_NAME,'')AS MEMBER_FIRST_NAME
,m.Guardian_Name
,LL.RECEIPT_BOOK
,LL.RECEIPT_NO
,M.POVERTY_RANK_CODE
,L.REPAYMENT_MODE_CODE
,L.Disbr_Date as DisbursementDate
,isnull(M.CNIC,M.OldNIC) as NIC
,M.Joining_Date
,L.PHASE
,L.CREDIT_PERIOD
,LM.Loanee_ID
,CO.UC_Code
,CO.City_Code,
COUNT_BIG(*) as cnt
FROM
dbo.CR_co CO
INNER join dbo.ADM_OFFICE O ON (co.office_code= O.office_code)
INNER join dbo.V_OFFICE_HIERARCHYDT i ON (O.office_code = i.level6_code and O.office_level = i.depthlevel)
inner join dbo.CR_CO_LOANEE_MASTER LM ON CO.CO_CODE = LM.CO_Code
INNER Join dbo.CR_CO_LOANEE L ON LM.Loanee_ID = L.Loanee_ID
INNER Join dbo.CR_CO_LOANEE_Ledger LL ON LM.Loanee_ID = LL.Loanee_ID
INNER join dbo.CR_co_member M on (LM.MEMBER_CODE= M.MEMBER_CODE)
INNER join dbo.CR_PACKAGE pck ON L.package_code = pck.package_code
INNER join dbo.CR_product product on (L.product_code = product.product_code)
group by i.LEVEL3_CODE
,i.LEVEL3_NAME
,i.LEVEL4_CODE
,i.LEVEL4_NAME
,i.LEVEL5_CODE
,i.LEVEL5_NAME
,i.LEVEL6_CODE
,i.LEVEL6_NAME
,LL.member_code
,LL.loan_apply_date
,L.Cheque_Date
,LL.DUE_DATE
,LL.RECEIPT_DATE
,L.PAYOFF_DATE
,LL.inst_no
,L.PACKAGE_CODE
,pck.PACKAGE_NAME
,L.PRODUCT_CODE
,product.PRODUCT_NAME
,EXCESS
,L.Borrower_NO
,L.SANCTION_NO
,co.CO_CODE
,co.CO_NAME
,co.CO_TYPE
,co.Office_Code
,m.GENDER
,m.MEMBER_FIRST_NAME
,M.MEMBER_Last_NAME
,Guardian_Name
,LL.RECEIPT_BOOK
,LL.RECEIPT_NO
,M.POVERTY_RANK_CODE
,L.REPAYMENT_MODE_CODE
,L.Disbr_Date
,m.CNIC
,m.OLDNIC
,M.Joining_Date
,L.PHASE
,L.CREDIT_PERIOD
,LM.Loanee_ID
,CO.UC_Code
,CO.City_Code
GO
CREATE UNIQUE CLUSTERED INDEX v_CR_WriteOffReceiptWise_Rpt_Ind ON v_CR_WriteOffReceiptWise_Rpt (RECEIPT_NO,RECEIPT_BOOK)
Any Help Please.
February 18, 2012 at 7:16 am
I've never messed with indexed view or schema binding, but the error message seems to indicate it does not like the aggregate functions. You could try wrapping the whole thing in a derived table like this...
USE tempdb
GO
IF OBJECT_ID('tempdb..v_CR_WriteOffReceiptWise_Rpt','v') IS NOT NULL
DROP VIEW v_CR_WriteOffReceiptWise_Rpt
GO
IF OBJECT_ID('tempdb..xtest','u') IS NOT NULL
DROP TABLE tempdb..xtest
GO
CREATE TABLE xtest
(
Col1 CHAR(1),
Col2 INT
)
GO
INSERT INTO xtest
SELECT 'A', 5 UNION ALL
SELECT 'A', 7 UNION ALL
SELECT 'B', 3 UNION ALL
SELECT 'B', 6
GO
Create view v_CR_WriteOffReceiptWise_Rpt with schemabinding AS
SELECT
Col1,
Col2
FROM
(
SELECT
Col1,
SUM(Col2) AS Col2
FROM dbo.xtest
GROUP BY Col1
) xtest
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
February 20, 2012 at 12:47 am
azhar.iqbal499 (2/18/2012)
I am creating Indexed views using SQL Server 2008R2. When I create index on the View it give me following error.Cannot create the clustered index 'v_CR_WriteOffReceiptWise_Rpt_Ind' on view 'PRSP_Live_010909_190112.dbo.v_CR_WriteOffReceiptWise_Rpt' 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 error message is quite explicit: the indexed view cannot contain expressions built from columns that are included in the GROUP BY clause, for instance "isnull(M.CNIC,M.OldNIC) as NIC" in your code. Create the view without these expressions (but including the columns needed to compute the expression), index this view, and then create a non-indexed view over the indexed view. The non-indexed view can contain expressions like "isnull(M.CNIC,M.OldNIC) as NIC" and the indexed view will give you the pre-aggregation advantages.
February 20, 2012 at 12:58 am
This demonstrates the problem and solution:
USE tempdb;
GO
CREATE TABLE dbo.Test (col1 integer NULL, col2 integer NULL, col3 integer NULL);
GO
CREATE VIEW dbo.V WITH SCHEMABINDING AS
SELECT
t.col1,
t.col2,
s = SUM(ISNULL(t.col3, 0)),
c = COUNT_BIG(*)
FROM dbo.Test AS t
GROUP BY
t.col1,
t.col2;
GO
-- No problem
CREATE UNIQUE CLUSTERED INDEX cuq ON dbo.V (col1, col2);
GO
-- Add a column (z) expression on group by columns
ALTER VIEW dbo.V WITH SCHEMABINDING AS
SELECT
z = ISNULL(t.col1, t.col2),
t.col1,
t.col2,
s = SUM(ISNULL(t.col3, 0)),
c = COUNT_BIG(*)
FROM dbo.Test AS t
GROUP BY
t.col1,
t.col2;
GO
-- Error 8668:
CREATE UNIQUE CLUSTERED INDEX cuq ON dbo.V (col1, col2);
GO
-- Back to the original definition:
ALTER VIEW dbo.V WITH SCHEMABINDING AS
SELECT
t.col1,
t.col2,
s = SUM(ISNULL(t.col3, 0)),
c = COUNT_BIG(*)
FROM dbo.Test AS t
GROUP BY
t.col1,
t.col2;
GO
-- Re-index
CREATE UNIQUE CLUSTERED INDEX cuq ON dbo.V (col1, col2);
GO
-- Non-indexed view on the indexed view
-- with expression 'z':
CREATE VIEW dbo.V2 WITH SCHEMABINDING AS
SELECT
z = ISNULL(v.col1, v.col2),
v.col1,
v.col2,
v.s,
v.c
FROM dbo.V AS v;
GO
-- Tidy up
DROP VIEW dbo.V2;
DROP VIEW dbo.V;
DROP TABLE dbo.Test;
September 19, 2015 at 4:07 am
Thanks Paul for a very good example. It helped me a lot.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply