Thread: Can't create an index on my view
12-01-04, 07:27 #1Registered User
- Join Date
- Nov 2004
Unanswered: Can't create an index on my view
I am trying to create an index on a view that joins two tables.
I get the classic error of course:
'Cannot index the view 'dbname.dbo.HJC_net'. It contains one or more disallowed constructs.'
Thing that gets me is that it all seems pretty normal stuff and I can't see what is stopping it.
Code is below and any help greatly appreciated.
CREATE VIEW dbo.HJC_net WITH SCHEMABINDING AS SELECT t_number FROM dbo.ticket_cancellations RIGHT OUTER JOIN dbo.tickets ON dbo.ticket_cancellations.tc_system_ref = dbo.tickets.t_number WHERE dbo.tickets.t_cancelled <> - 1 OR -- Add all cancellation codes that are to be excluded from the NET view below (dbo.ticket_cancellations.tc_cancellation_code <> 83943 AND dbo.ticket_cancellations.tc_cancellation_code <> 83946) GO -- Create a clustered index, it MUST be unique CREATE UNIQUE CLUSTERED INDEX t_number_unique ON HJC_net(t_number)
Last edited by Samaritan; 12-01-04 at 07:35.
12-01-04, 08:19 #2Registered User
- Join Date
- Jul 2003
- San Antonio, TX
The SELECT statement in the view cannot contain these Transact-SQL syntax elements:
Outer or self joins
..."The data in a record depends on the Key to the record, the Whole Key, and
nothing but the Key, so help me Codd."