asked on

Indexing a View

Hi all i am trying to index a view, i have switched ANSI_nulls and Quoted_Identifier ON, Schema Binding is on, the view is not using another view as its source, it is using a table but i am getting this error

"The optimizer cannot use the index because the select list of the view contains a non-aggregate expression"

any idea's?

is it because i am using a CASE statement - can i index a view with a case statement

Hilaire

Please post view definition
sp_helptext yourview

apresto

ASKER

Thanks for the reply

im not sure what you mean by definition but here is the view:

----------------------------------
CREATE VIEW qryCustomerDataMatchType_Refined WITH SCHEMABINDING
AS
SELECT
      PAFMatchType,
      CustomerNumber,
      CustomerID,
      DomicileID,
      PersonID,
      CDID,
      Title,
      Initial,
      Surname,
      CASE
       WHEN (PAFMatchType='Corrected' OR PAFMatchType='Full') THEN PAFPostCode
       ELSE CustomerPostcode END AS Postcode,
      CASE
       WHEN (PAFMatchType='Corrected' OR PAFMatchType='Full') THEN PAFAdd1
       ELSE CustomerAdd1 END AS Add1,
      CASE
       WHEN (PAFMatchType='Corrected' OR PAFMatchType='Full') THEN PAFAdd2
       ELSE CustomerAdd2 END AS Add2,
      CouncilTaxBand,
      CarMake,
      CarModel,
      ModelDerivative,
      VehicleLicenceNumber,
      RegistrationDate,
      DateOfSale,
      SaleMansName,
      CustomerPostCode,
      GoneAwayInitialAndSurname,
      GoneAwaySurnameOnly,
      MortaScreenInitialAndSurname,
      MortaScreenSurnameOnly,
      BereavementInitialAndSurname,
      BereavementSurnameOnly,
      MPSSurnameOnly,
      Duplicates,
      Businesses,
      Telephone1,
      Telephone2,
      Telephone3,
      Telephone4,
      TPS1,
      TPS2,
      TPS3,
      TPS4,
      MatchType,
      NewOrUsed
FROM
      dbo.tblCustomerDataMatchTypes
--------------------------

ASKER CERTIFIED SOLUTION

rafrancisco

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

apresto

ASKER

i thought that was the case, oh well, thanks rafrancisco

Apresto