Cannot create the clustered index ind on view V 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

  • [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.

  • 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.

  • 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.

  • 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;

  • 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