none
Index on View

    Frage

  • SSMS is returning the error below when I try to create a view:

    Msg 8668, Level 16, State 0, Line 1

    Cannot create the clustered index 'IX_TPiDs' on view 'Schema1.dbo.View_Tbl_A' 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.

    Please see my view definition (it created fine)

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE view [dbo].[View_Tbl_A] With Schemabinding
    AS
    SELECT	TPID,
    	Client,
    	PFN + ' ' + PLN AS [PN],
    	PFTIN,
    	PFax,
    	MFN + ' ' + MLN AS [MN],
    	MCC AS [MID],
    	Count_Big(*) AS Cnt			
    FROM		Tbl_A
    WHERE		TPID IS NOT NULL
    GROUP BY	TPID,Client,PFN,PLN,PFTIN,PFax,
    			MFN,MLN,MCC
    GO
    SET ANSI_NULLS OFF
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    Here is My index denition:

    Create UNIQUE Clustered INDEX IX_TPiDs ON dbo.[View_Tbl_A](TPID)
    Go

    Can someone please help?


    Dienstag, 19. März 2013 15:05

Antworten

  • Instead of grouping by the individual columns, you need to group by the same expressions as in your SELECT list:

    CREATE view [dbo].[View_Tbl_A] With Schemabinding
    AS
    SELECT	TPID,
    	Client,
    	PFN + ' ' + PLN AS [PN],
    	PFTIN,
    	PFax,
    	MFN + ' ' + MLN AS [MN],
    	MCC AS [MID],
    	Count_Big(*) AS Cnt			
    FROM		dbo.Tbl_A
    WHERE		TPID IS NOT NULL
    GROUP BY	TPID,Client,PFN + ' ' + PLN,PFTIN,PFax,
    			MFN + ' ' + MLN,MCC
    GO

    • Als Antwort markiert Tshindaye Dienstag, 19. März 2013 15:52
    Dienstag, 19. März 2013 15:46

Alle Antworten

  • Instead of grouping by the individual columns, you need to group by the same expressions as in your SELECT list:

    CREATE view [dbo].[View_Tbl_A] With Schemabinding
    AS
    SELECT	TPID,
    	Client,
    	PFN + ' ' + PLN AS [PN],
    	PFTIN,
    	PFax,
    	MFN + ' ' + MLN AS [MN],
    	MCC AS [MID],
    	Count_Big(*) AS Cnt			
    FROM		dbo.Tbl_A
    WHERE		TPID IS NOT NULL
    GROUP BY	TPID,Client,PFN + ' ' + PLN,PFTIN,PFax,
    			MFN + ' ' + MLN,MCC
    GO

    • Als Antwort markiert Tshindaye Dienstag, 19. März 2013 15:52
    Dienstag, 19. März 2013 15:46
  • Many Thanks Mike, it's now working !

    Dienstag, 19. März 2013 15:52