Posted by Bethany Holliday on 11/12/32 11:34

Hi All,

I'm hoping someone can help me. I think I'm missing something very
basic. I'm trying to put a clustered index on a view that I have
created. I keep getting the error:

Server: Msg 8668, Level 16, State 1, Line 1
An index cannot be created on the view 'cew_avwage_uscnty' because the
select list of the view contains a non-aggregate expression.

Here is the create view statement:

CREATE view dbo.cew_avwage_uscnty
with schemabinding
as
select statefips, countyfips, naics_code, sum(disc * 0) as disc,
data_year, sum(qtr_payroll)/((sum(emp1+emp2+emp3))/12) as avwage
from dbo.qcew_own_n where year= '2004'
and (ownership = '0'
and (naics_code like '__' or naics_code like '__-__')
or (ownership = '5' and naics_code = '10'))
and countyfips <> '999'
and naics_code <> '99'
and ((disc = '0') or (disc <> '0' and (emp1 <> 0 or emp2 <> 0 or emp3 <>
0)))
group by statefips, countyfips, naics_code, data_year

Here is the create index statement I am using:

create unique clustered index main_cdx
on dbo.cew_avwage_uscnty (statefips, countyfips, naics_code, year)

Each field that I have listed in the select statement MUST appear in the
view. The disc field needs to be set = 0 and the data_year field needs
to be set = 2004. Initially I had select statement for disc and
data_year set as, disc = '0', year = '2004' .. but thought this was
giving me my problem. I changed it and am still getting the same error?
Is my problem because of the fields that I am grouping by? Any advice
would be very much appreciated. I'm trying to avoid creating an extract
table, as these data are updated regularly - so I would like to only
update the base table and have the view refresh itself.

Thanks again!

Bethany




*** Sent via Developersdex http://www.developersdex.com ***

[Back to original message]