Error creating index on view

  • I have built the following view...

    CREATE VIEW V_BUDGET_INSERTION WITH SCHEMABINDING AS

    SELECT DISTINCT B.FISCAL_PERIOD,

      B.PROJECT,

      B.STORE4,

      S.NAME,

      B.AP_CODE,

      R.MEDIA_DESC,

      B.IS_DEFAULT,

      B.NEWS_RATE,

      B.TMC_RATE,

      B.NEWS_QTY,

      B.TMC_QTY,

      B.TOTAL_QTY,

      B.MEDIA_COST

    FROM dbo.R_BUDGET_INSERTION B

    INNER JOIN dbo.STORES S

    ON B.STORE4 = S.STORE4

    INNER JOIN dbo.R_REMIT R

    ON B.AP_CODE = R.AP_CODE

    I am trying to create the following Unique Clustered index...

    CREATE UNIQUE CLUSTERED INDEX pk_project_store_ap ON dbo.V_BUDGET_INSERTION(PROJECT,STORE4,AP_CODE)

    I get the following error...

    Server: Msg 1936, Level 16, State 1, Line 1

    Cannot index the view 'MKTGDB.dbo.V_BUDGET_INSERTION'. It contains one or more disallowed constructs.

    Each of the underlying tables has a primary key.  The table R_Budget_Insertion enforces a primary key on Project,Store4,AP_Code.  I am trying to create a simple presentation/editing tool for the table R_Budget_Insertion using VB.NET.  VB.NET requires a unique key on the view to work properly.

    Any takers?

     

  • I found this in books online.  Maybe the DISTINCT is causing the error.

     

    Restrictions on indexed views

    The SELECT statement defining an indexed view must not have the TOP, DISTINCT, COMPUTE, HAVING, and UNION keywords. It cannot have a subquery.

     

    Hope this helps

    Mark

  • Wow...I must be blind or today is Friday.

     

    Thanks for the extra set of eyes!!!!

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply