Update failed because it contains a derived or constant field 1

[JohnBates]

Hi everyone,

I have a situation I don't understand.

The following UPDATE stmt gives this error:

Server: Msg 4406, Level 16, State 2, Line 1
Update or insert of view or function 'dbo.vwTblClaims_stampTblClaimsUpdate' failed because it contains a derived or constant field.

Can anyone interpret this message for me?

Thanks, John


BEGIN
UPDATE dbo.vwTblClaims_stampTblClaimsUpdate
SET totalclaim = claimed, totalapproved = approved, totalpaid = paid, balance = balpending, isPaid = CONVERT(binary, ispaidfrom), isAuth = isauthFrom,
authBy = authbyfrom, dtmAuth = dtmauthfrom, paidBy = paidbyfrom, dtmPaid = dtmpaidfrom, dtmreceived = claimreceived, receivedBy = recby,
misc2 = status, lamto = CONVERT(varchar, lamfrom), lmgto = CONVERT(varchar, lmgfrom)
END

 

[vongrunt]

And DDL (CREATE VIEW stuff) for vwTblClaims_stampTblClaimsUpdate is... ?

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[JohnBates]

..... oops.

CREATE VIEW dbo.vwTblClaims_stampTblClaimsUpdate
AS
SELECT dbo.vwTblClaims_convert.totalclaim, dbo.vwTblClaims_convert.totalapproved, dbo.vwTblClaims_convert.totalpaid, dbo.vwTblClaims_convert.balance,
dbo.vwTblClaims_convert.isPaid, dbo.vwTblClaims_convert.isAuth, dbo.vwTblClaims_convert.authBy, dbo.vwTblClaims_convert.dtmAuth,
dbo.vwTblClaims_convert.paidBy, dbo.vwTblClaims_convert.dtmPaid, dbo.vwTblClaims_convert.dtmreceived, dbo.vwTblClaims_convert.receivedBy,
dbo.vwTblClaims_convert.misc2, dbo.vwTblClaims_stampTblClaims.totalClaim AS claimed,
dbo.vwTblClaims_stampTblClaims.totalApproved AS approved, dbo.vwTblClaims_stampTblClaims.totalPaid AS paid,
dbo.vwTblClaims_stampTblClaims.balance AS balpending, dbo.vwTblClaims_stampTblClaims.misc2 AS status,
dbo.vwTblClaims_stampTblClaims.dtmReceived AS claimreceived, dbo.vwTblClaims_stampTblClaims.receivedBy AS recby,
dbo.vwTblClaims_stampTblClaims.isAuth AS isauthFrom, dbo.vwTblClaims_stampTblClaims.dtmAuth AS dtmauthfrom,
dbo.vwTblClaims_stampTblClaims.authBy AS authbyfrom, dbo.vwTblClaims_stampTblClaims.ispaid AS ispaidfrom,
dbo.vwTblClaims_stampTblClaims.dtmPaid AS dtmpaidfrom, dbo.vwTblClaims_stampTblClaims.paidBy AS paidbyfrom,
dbo.vwTblClaims_convert.lam AS lamto, dbo.vwTblClaims_stampTblClaims.lam AS lamfrom, dbo.vwTblClaims_convert.lmg AS lmgto,
dbo.vwTblClaims_stampTblClaims.lmg AS lmgfrom
FROM dbo.vwTblClaims_convert INNER JOIN
dbo.vwTblClaims_stampTblClaims ON CONVERT(varchar, dbo.vwTblClaims_convert.controlnum) = CONVERT(varchar,
dbo.vwTblClaims_stampTblClaims.controlnum) AND dbo.vwTblClaims_convert.dlrnum = dbo.vwTblClaims_stampTblClaims.dlrnum

 

[vongrunt]

Suppose one of columns in a view is declared as constant - for example, 1 AS blah. Attempt to UPDATE that column makes no sense - and server throws an error from above.

Ditto for computed columns. If FullName is declared as LastName+', '+FirstName then UPDATE is not possible. Server simply cannot map updated value to column(s) in physical tables behind view.

Ditto #2 for aggregate columns. If totalapproved is generated by SUM() or something, UPDATE is not possible. I guess vwTblClaims_convert has some aggregate functions, right?

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[JohnBates]

vongrunt,

This is vwTblClaims_convert. As you can see, there are no aggregrate functions... but I admit there may be some elsewhere. (Believe me, I didn't design this!)

I appreciate your time. John

CREATE VIEW dbo.vwTblClaims_convert
AS
SELECT CASE WHEN misc1 = 'C' THEN CONVERT(varchar, crnum) ELSE CONVERT(varchar, controlnum)
END
AS controlnum, userID, dtmCreated, dlrnum,
dlrname, address, city, state, zip, phone, email, funds, totalclaim, totalapproved, totalpaid, reason, balance, programYear, numrows, active,
isApproved, isPosted, ynApproval, comments, comments_appr, isRCC, isPB, asmID, asmName, asmAddress, asmCity, asmState, asmZip, asmEmail, asmPhone, dtmChanged, changedBy, misc1, misc2, misc3, misc4, misc5, dtmreceived, receivedBy, crnum, CONVERT(varchar, lam) AS lam,
CONVERT(varchar, lmg) AS lmg, paymentNum, isManual, docFileName, isAuth, dtmAuth, authBy, isPaid, dtmPaid, paidBy, ID
FROM dbo.tblClaims

 

[JohnBates]

what about this:

CREATE VIEW dbo.vwTblClaims_ClaimsConvert
AS
SELECT TOP 100 PERCENT CASE WHEN LEFT([Clm Spec 1], 3) = 'PBA' THEN CASE WHEN RIGHT([clm spec 1], 3) = 'swo' THEN LEFT(RIGHT([clm spec 1],
len([clm spec 1]) - 3), len([clm spec 1]) - 6) ELSE CASE WHEN RIGHT([clm spec 1], 2) = 'wo' THEN LEFT(RIGHT([clm spec 1], len([clm spec 1]) - 3),
len([clm spec 1]) - 5) ELSE CASE WHEN RIGHT([clm spec 1], 1) = 'w' THEN LEFT(RIGHT([clm spec 1], len([clm spec 1]) - 3), len([clm spec 1]) - 4)
ELSE RIGHT([clm spec 1], len([clm spec 1]) - 3) END END END ELSE CASE WHEN LEFT([Clm Spec 1], 2) = 'PB' THEN CASE WHEN RIGHT([clm spec 1],
3) = 'swo' THEN LEFT(RIGHT([clm spec 1], len([clm spec 1]) - 2), len([clm spec 1]) - 5) ELSE CASE WHEN RIGHT([clm spec 1], 2)
= 'wo' THEN LEFT(RIGHT([clm spec 1], len([clm spec 1]) - 2), len([clm spec 1]) - 4) ELSE CASE WHEN RIGHT([clm spec 1], 1)
= 'w' THEN LEFT(RIGHT([clm spec 1], len([clm spec 1]) - 2), len([clm spec 1]) - 3) ELSE RIGHT([clm spec 1], len([clm spec 1]) - 2)
END END END ELSE CASE WHEN RIGHT([clm spec 1], 3) = 'swo' THEN LEFT([clm spec 1], len([clm spec 1]) - 3) ELSE CASE WHEN RIGHT([clm spec 1],
2) = 'wo' THEN LEFT([clm spec 1], len([clm spec 1]) - 2) ELSE CASE WHEN RIGHT([clm spec 1], 1) = 'w' THEN LEFT([clm spec 1], len([clm spec 1]) - 1)
ELSE [clm spec 1] END END END END END AS controlnum, [Dlr Num], Promo, [Trans Code], [Proc Date], [Invoice Num], [Clm Date], [Amt Claimed],
[Amt Appr], [Amt Paid], [Ad From], [Ad To], Media, [Media Desc], [Audit 1], [Audit 2], [Audit 3], [Audit 4], [Audit 5], [Audit 6], [Audit 7], [Audit 8], [Audit 9],
[Audit 10], [Audit 11], [Audit 12], [Prod Code 1], [Prod Code 1 %], [Prod Code 1 Amt], [Prod Code 1 Adv Amt], [Prod Code 2], [Prod Code 2 %],
[Prod Code 2 Amt], [Prod Code 2 Adv Amt], [Prod Code 3], [Prod Code 3 %], [Prod Code 3 Amt], [Prod Code 3 Adv Amt], [Prod Code 4], [Prod Code 4 %],
[Prod Code 4 Amt], [Prod Code 4 Adv Amt], [Prod Code 5], [Prod Code 5 %], [Prod Code 5 Amt], [Prod Code 5 Adv Amt], [Prod Code 6], [Prod Code 6 %],
[Prod Code 6 Amt], [Prod Code 6 Adv Amt], [Prod Code 7], [Prod Code 7 %], [Prod Code 7 Amt], [Prod Code 7 Adv Amt], [Prod Code 8], [Prod Code 8 %],
[Prod Code 8 Amt], [Prod Code 8 Adv Amt], [Prod Code 9], [Prod Code 9 %], [Prod Code 9 Amt], [Prod Code 9 Adv Amt], [Prod Code 10], [Prod Code 10 %],
[Prod Code 10 Amt], [Prod Code 10 Adv Amt], Comment, [Received Date], [Last Modified], [Prior Appr Num], [AltPayee Flag], [AltPayee Number], Ratio,
[Number Of Ads], [Amt Applied], UM, [Audited Amt], [Rated Ads], [Branch Number], Operator, [Bal Pending], [Paid From Pending], [Store Num],
[Clm Spec 1], [Clm Spec 2], [Dept Num], [Doc Type], [Det Spec 1], [Det Spec 2], [Det Spec 3], [Det Spec 4], [Det Spec 5], [Det Spec 6], [Size Claimed],
[Size Appr], [Media Zip], Color, [Check Num], [Check Date], [Rate Appr], [Void Date], [Void Comment], Status, [Org Control Num], Locked, [Rec Num],
txtTracking, ysnLMGCR, FileNumberIn, FileNumberOut, systimestamp, FileLineNumber, [Control Num]
FROM dbo.Claims
WHERE (LEFT([Dlr Num], 7) <> '0000000') AND ([Trans Code] <> 'N') AND ([Trans Code] <> 'V') AND ([Trans Code] <> 'Q') AND ([Trans Code] <> 'T') AND
(LEFT(Promo, 2) = '05' OR
LEFT(Promo, 2) = '06')

 

[vongrunt]

My guess is some of affected columns in tblClaims are computed. Run this:

declare @tbl sysname; set @tbl = 'tblClaims'

select column_name
from information_schema.columns
where table_name = @tbl
	and columnproperty(object_id(@tbl), column_name, 'IsComputed') = 1
	and column_name in ('totalclaim', 'totalapproved', 'totalpaid', 'balance', 'isPaid', 'isAuth')

Any record?

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[JohnBates]

no. 0 recs returned.

 

[vongrunt]

Oh, I forgot UPDATE list is a bit longer...

Remove last AND condition (column names). Is there any record (=computed column in tblClaims)?

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[JohnBates]

ok, I get what you're after now.

I ran:
declare @tbl sysname; set @tbl = 'tblClaims'
select column_name
from information_schema.columns
where table_name = @tbl
and columnproperty(object_id(@tbl), column_name, 'IsComputed') = 1

0 rows returned.

I'm really lost on this one (as you can see).John

 

[JohnBates]

This *&#! thing was working fine last week. Maybe something is hosed up in the SQL Server (MS) tables.

I'll run some dbcc (Update Statistics, updateusage) commands - it can't hurt anything.

John

 

[vongrunt]

OK, lemme illustrate typical situations when that error happens:

create table foo (a int, b int, ab2 as (a+b)/2.0)
insert into foo values(1, 2)
go

create view vfoo as
select a, b, ab2, sqrt(a*a+b*b) as c
from foo
go

select * from vfoo

-- works
update vfoo set a= 3, b=4
select * from vfoo

-- fails - computed column in physical table
update vfoo set ab2 = 4
-- fails - computed column in view
update vfoo set c = 13

drop table foo
drop view vfoo

Looks like only columns affected by UPDATE matter... so maybe you can narrow search area for hunting

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why