March 11, 2005 at 2:04 pm
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?
March 11, 2005 at 2:26 pm
I found this in books online. Maybe the DISTINCT is causing the error.
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
March 11, 2005 at 2:32 pm
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