Results 1 to 5 of 5

Thread: Indexed View

  1. #1
    Join Date
    Jun 2004
    Posts
    8

    Angry Unanswered: Indexed View

    Hi

    I am trying toe create an indexed view but cannot seem to get it right.

    CREATE VIEW dbo.D_Policy_View with schemabinding
    AS
    SELECT Policy_ID, Environment_Code, CoB, Sub_CoB, Policy_No, Version_No
    FROM dbo.D_Policy
    WHERE (Policy_ID IN
    (SELECT MAX(Policy_ID)
    FROM dbo.d_Policy
    GROUP BY Environment_Code, COB, Policy_No, SUB_COB))

    I have read on BoL that MAX is not allowed but don't know of any other way to get the latest record??

    please help

  2. #2
    Join Date
    Jun 2003
    Location
    cape town, south africa
    Posts
    102
    hi

    cant you make your subquery (grouping) another view, then inner join to it?
    (think it is the subqueries MAX it is complaining about - not the top one)

    Des

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What's with the GROUP BY?

    Code:
    USE Northwind
    GO
    
    CREATE VIEW myView99
    AS
    SELECT * FROM Orders WHERE OrderId = (SELECT MAX(OrderId) FROM Orders)
    GO
    
    SELECT * FROM myView99
    GO
    
    DROP VIEW myView99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jun 2004
    Posts
    8
    I tried the Join but on sql server200 BOL it states that joins cannot be used for an indexed view

  5. #5
    Join Date
    Jun 2004
    Posts
    8
    The group by is used as I need the max policy_id per environment,cob,sub_cob,Policy_no

Posting Permissions

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