Wednesday, March 7, 2012

Cant create an index on my view

G'day all.

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)BOL:

The SELECT statement in the view cannot contain these Transact-SQL syntax elements:
...
Outer or self joins
...

No comments:

Post a Comment