ID Number: Q75881
1.11
OS/2
buglist1.11
Summary:
Problem ID: PRSQL109002
SYMPTOMS
When attempting to update a column that has a data type of FLOAT,
INT, or MONEY with a view's column that is defined with a SUM
aggregate, the following error message is returned:
Msg 206, Level 16, State 1
Operand type clash: datatype is incompatible with datatype
The problem does not occur for the TINYINT or SMALLINT data types.
WORKAROUND
This problem can be avoided by using the CONVERT function on the
SUM aggregate value in the view column. For example, the following
script will return the error described above:
create table t1 (col1 float)
go
create view t1_view
as
select col1 = sum(col1) from t1
go
create table t2 (col1 float)
go
insert into t1 values (1.1)
insert into t1 values (1.1)
insert into t2 values (0)
go
update t2
set t2.col1 = t1_view.col1
from t2, t1_view
where t2.col1 = 0
go
select * from t2
go
Modifying the update statement as follows will resolve the problem:
update t2
set t2.col1 = convert(float, t1_view.col1)
from t2, t1_view
where t2.col1 = 0
STATUS
Microsoft has confirmed this to be a problem in SQL Server version
1.11. We are researching this problem and will post new information
here as it becomes available.