Aveaging records with same key set and diff. valu

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Aveaging records with same key set and diff. valu

Author

Topic

goligol
Posting Yak Master

128 Posts

Posted - 2011-05-31 : 10:17:45

I am trying to use the below statement to remove the duplicated records and also to get the average value of V0 on the records which have the same key sets of (ODCM):

SELECT O, D, C, M, AVG(V0) V0 INTO #temp FROM [dev].[V0] GROUP BY O, D, C, M
TRUNCATE TABLE [dev].[V0]
INSERT INTO [dev].[V0] SELECT * FROM #temp
DROP TABLE #temp

it gives me the following error:

Msg 4406, Level 16, State 1, Line 3
Update or insert of view or function 'dev.GAMS_Init_Var' failed because it contains a derived or constant field.

and I have to mention that the view dev.V0 is made from the view dev.GAMS_Init_Var as:
create view [dev].[V0]
as
SELECT O
,D
,M
,C
,V0

FROM [dev].[GAMS_Init_Var]
where V0 is not null

GO

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-31 : 11:03:46

The problem is with GAMS_Init_Var.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

goligol
Posting Yak Master

128 Posts

Posted - 2011-05-31 : 11:06:54

what kind of problem it could be? Do you know

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-31 : 11:07:48

The view [dev].[V0] (or another view from which this view is created) has a constant column or derived column. For example, if the view is defined as "select o, avg(v0) avgv0 from aTable group by o", then you cannot update this view. The presence of the computed (avg(v0) in this example) or constant column makes the view un-updateable.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-31 : 11:08:33

try this
create table d (i int, j int)
create view dv as select *, k = 1 from d
create view dv1 as select i,j, k from dv
insert dv1 select 1,1,1

should give the same error because k is a constant in dv - same if it was calcaulted on other columns.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jfarrugia
Yak Posting Veteran

55 Posts

Posted - 2011-05-31 : 11:09:49

what is GAMS_Init_Var anyway?

As far as i know you can not simply TRUNCATE a view

Where software development knowledge meets the reader

goligol
Posting Yak Master

128 Posts

Posted - 2011-05-31 : 12:07:10

GAMS_Init_Var is a view created by unionig 12 tables.
How should I create a table out of this view to be able to truncate it.

Thank you

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-31 : 12:18:46

You can't truncate a view. You can delete from it if it is updateable or you can truncate each of the underlying tables - but that's not your main problem (unless you have a partitioned view, but why not use a partitioned table instead).
Read about views in bol and get used to writing test code

create table d (i int, j int)
create table e (i int, j int)
create view dv as select * from d union select * from e

These should all fail with fairly explanatory error messages.
truncate table dv
insert dv select 1,1
delete dv

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

goligol
Posting Yak Master

128 Posts

Posted - 2011-05-31 : 12:27:25

@nigelrivett
Thnak you. I tried the first two command statements. When I was running the last command it gives the following error:

Msg 156, Level 15, State 1, Procedure dv1, Line 2
Incorrect syntax near the keyword 'insert'.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-31 : 12:54:29

Did you run the last three individually?

It's complaining about an error in the procedure dv1 - on line 2,

I think you are talking about my previous post. You need to run each line separately otherwise it will think the insert tsatement is part of the view definition - or put a "go" between the two statements.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

goligol
Posting Yak Master

128 Posts

Posted - 2011-05-31 : 13:43:46

@Nigelrivett,
Although I ran them separately, it creates dv but the last command for inserting to dv1 gives the same error.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-31 : 13:57:22

That's the point. It gives you a way to find out what's causing the error by simplifying the code.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dujiaojing0
Starting Member

12 Posts

Posted - 2011-06-01 : 06:26:01

Very good explaination, thanks nigelrivett.

unspammed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources