Re: "disallowed construct" on indexed view

From: ChrisR (bla_at_noemail.com)
Date: 12/23/04 

Date: Thu, 23 Dec 2004 10:45:16 -0800

Now Im really confused.

alter view tmpSpeed
with schemabinding
as
 SELECT Client.ClientKey,
        Client.CardReference,
        COUNT(ClientPIN.ClientPIN) As TotalReloadsInPast24Hours,
        SUM(ClientPIN.ClientPINValue) AS
AmountReloadedInPast24Hours,count_big(*) as count
   FROM dbo.ClientPIN
  INNER JOIN dbo.Client ON Client.ClientKey = ClientPIN.ClientKey
  INNER JOIN dbo.Package ON ClientPIN.PackageKey = Package.PackageKey AND
Package.IsReload = 1
   GROUP BY Client.ClientKey, Client.CardReference
   HAVING COUNT(ClientPIN.ClientPIN) > 5 OR SUM(ClientPIN.ClientPINValue) >
2400
go

There is only 1 HAVING clause. I commented out the whole line and it still
failed. Why would would this be?

"Gert-Jan Strik" <sorry@toomuchspamalready.nl> wrote in message
news:41CB05CB.B9B1575E@toomuchspamalready.nl...
> Chris,
>
> Indexed views are constructed in such a way, that it should be possible
> to process each individual row change, without having to access other
> rows.
>
> Both HAVING clauses violate this rule.
>
> What you could do is remove the HAVING clause so you can index tmpSpeed,
> and then create another view, something like:
>
> CREATE VIEW tmpSpeed2 AS
> SELECT ClientKey, CardReference, TotalReloadsInPast24Hours,
> AmountReloadedInPast24Hours
> FROM dbo.tmpSpeed
> WHERE ( TotalReloadsInPast24Hours > 5
> OR AmountReloadedInPast24Hours > 2400 )
>
> Hope this helps,
> Gert-Jan
>
> ChrisR wrote:
> >
> > Im trying to create an Index on a view and getting:
> >
> > Server: Msg 1936, Level 16, State 1, Line 1
> > Cannot index the view 'Northwind.dbo.tmpSpeed'. It contains one or more
> > disallowed constructs.
> >
> > Whats the dealio?
> >
> > alter view tmpSpeed
> > with schemabinding
> > as
> > SELECT Client.ClientKey,
> > Client.CardReference,
> > COUNT(ClientPIN.ClientPIN) As TotalReloadsInPast24Hours,
> > SUM(ClientPIN.ClientPINValue) AS
> > AmountReloadedInPast24Hours,count_big(*) as count
> > FROM dbo.ClientPIN
> > INNER JOIN dbo.Client ON Client.ClientKey = ClientPIN.ClientKey
> > INNER JOIN dbo.Package ON ClientPIN.PackageKey = Package.PackageKey
AND
> > Package.IsReload = 1
> > GROUP BY Client.ClientKey, Client.CardReference
> > HAVING COUNT(ClientPIN.ClientPIN) > 5 OR
SUM(ClientPIN.ClientPINValue) >
> > 2400
> > go
> >
> > create unique clustered index PKtmpSpeed on
> > tmpSpeed(ClientKey,CardReference)
> > go
> >
> > --
> > SQL2K SP3
> >
> > TIA, ChrisR