fails because of a derived or constant field

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

fails because of a derived or constant field

Author

Topic

tariq2
Posting Yak Master

125 Posts

Posted - 2011-10-05 : 09:45:09

Hi,
I am receiving an error with the following code.
'Msg 4406, Level 16, State 1, Line 1
Update or insert of view or function 'CTE1' failed because it contains a derived or constant field.'

Many thanks in advance

WITH CTE1 AS
(

select periodkey,bukey,ictypekey,SUM(aot) as AOGMAOT from a_AOGM where PeriodKey = 105
and BUKey in (select BUKey from D_BusinessUnits
where SBLID = 'MKG' and Year = '2011' and TerritoryName = 'UK')
GROUP BY periodkey,bukey,ictypekey with rollup

),CTE2 AS
(

SELECT ICTypeKey,BUKey,SUM(aot) as AOTAOT FROM A_AOT where PeriodKey >=97 and PeriodKey <=105
and BUKey in (select BUKey from D_BusinessUnits
where SBLID = 'MKG' and Year = '2011' and TerritoryName = 'UK') AND LedgerKey = 20
group by ICTypeKey,BUKey with rollup
)

update CTE1
set cte1.aogmaot = cte2.aotaot
FROM CTE1 a inner join CTE2 b
on a.BUKey = b.BUKey
and a.ictypekey = b.ictypekey

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-10-05 : 10:07:35

Please think about what you are trying to do.
Makes it sense? No.

Maybe you can explain what you want and why you want it...

No, you're never too old to Yak'n'Roll if you're too young to die.

tariq2
Posting Yak Master

125 Posts

Posted - 2011-10-05 : 10:15:42

Yes, apologies if this is unclear. I shall try to clarify
For specific bukeys and ictypekeys
The AOT value in A_AOGM table is incorrect, therefore I would like to update these
with the AOT values in the A_AOT table.

Many thanks, I hope this makes the issue clear

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-10-05 : 10:19:15

You only can correct the underlying values of aot but you can't change the result of a SUM() directly.

No, you're never too old to Yak'n'Roll if you're too young to die.

tariq2
Posting Yak Master

125 Posts

Posted - 2011-10-05 : 10:27:14

Okay, thank you Webfred

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources