Need Help on VIEWS - SQL Server Forums

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

Need Help on VIEWS

Author

Topic

javid.alimohideen
Starting Member

2 Posts

Posted - 2007-04-17 : 17:02:38

Can someone tell me when I would get an error like the one below:
"Update or insert of view or function failed because it contains a derived or constant field."

Here is how I create a VIEW

CREATE VIEW vSample
AS
SELECT ilm.ITEM_ID,
CAST (ilm.CURRENT_QUANTITY AS INT) CURRENT_QUANTITY
FROM SAMPLE_1 AS ilm INNER_JOIN SAMPLE_2 AS ilm2
on ilm.ID = ilm2.ID

Now, I am trying to update data using the view
UPDATE [vSample] SET [CURRENT_QUANTITY] = 598.00 WHERE (1 = 1)

I have tried casting the number 598.00 as int and no luck.
Can someone please help or guide me on this problem?

Thanks,
Javid

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2007-04-17 : 17:18:23

The error message says it all..

The CAST operation causes SQL Server to interpret it as a derived column in the view.

Don't CAST in the view...

DavidM

Production is just another testing cycle

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-04-17 : 18:49:12

Many time views are cannot be updated, either due to manipulations of the returned columns or because of the nature or complexity of the joins involved. If you cannot simplify your view or or its output, then put an INSTEAD OF trigger on the view containing the logic necessary to update the underlying tables.

e4 d5 xd5 Nf6

javid.alimohideen
Starting Member

2 Posts

Posted - 2007-04-17 : 23:08:39

Hi,
Thanks for your immediate response. Can you give me an example of how to use the instead of trigger. I am unable to modify the view because in my data presentation I have to display the decimal value as an int value and I found CAST as int, the only way.

Thanks again,
Javid

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-04-18 : 11:58:07

What are you using to display the results that you cannot cast or format the value at the interface?

An INSTEAD OF trigger runs on a table "intead of" the regular insert, update, or delete. Books Online has syntax, descriptions, and examples.

e4 d5 xd5 Nf6

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources