Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2004
    Posts
    4

    Question Unanswered: Can't 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.

    Code:
    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.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    BOL:

    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."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •