(Non) Updatable view problem

16 Dec 2005 2:53 PM

mfan

Hi there!

I created this view:

select
pk,
name,
(   select count(*)
      from dbo.Orders ord
      where ord.cust_fk = cust.pk
   ) as order_count
from dbo.Customer cust

When, being in the Design of the view in SQL Server Enterprise Manager,
running it, I can make changes to the field 'dscr'.

However, when I open the view from viewname -> double click -> Open
View -> Return All Rows, I _cannot_ make changes.

why that?

(I am using Sql Server 8.0 Enterprise Edition)

Any hint appreciated!

16 Dec 2005 3:19 PM

ML

Open and Design are two different things.

When you open a view, Enterprise Manager displays the result set and enables
you to use the view in your queries.

If you need to alter the view, you need to go to design mode, or better yet
(much better if you ask me) use Query Analyzer to design views.

ML

---
http://milambda.blogspot.com/

16 Dec 2005 3:53 PM

mfan

When I said:

> [...] However, when I open the view from viewname -> double click -> Open
> View -> Return All Rows, I _cannot_ make changes [...]

I meant, I cannot edit and alter the contents of the 'dscr' field, not
the view's structure. Well, why does this _not_ happen in design view?

Is the only way for this view to be updatable to add instead of
triggers?

16 Dec 2005 4:31 PM

ML

Oh, I see. You want to update data.

Don't use EM for that, that's poor practice. Use T-SQL - make your work
transparent, repeatable and consistent.

There are some limitations to the updates against views, and I'm pretty sure
EM can't handle those properly. Look up details in Books Online.

ML

---
http://milambda.blogspot.com/

16 Dec 2005 4:32 PM

Scott Morris

> I meant, I cannot edit and alter the contents of the 'dscr' field, not
> the view's structure. Well, why does this _not_ happen in design view?
>
> Is the only way for this view to be updatable to add instead of
> triggers?

First, there are certain restrictions that must be met before a view is
considered updateable. Please review the documentation to verify that you
understand these restrictions and that your view meets these requirements.
Hint - it does not (at least based on the DDL you included - which I suspect
may not be complete).

Second, EM is not a good editing tool as it has it own quirks, bugs and
issues with respect to how it works. If you want unambiguous determination
of whether your view is updateable or not, issue the insert/update/delete
statement within QA (or a similar tool) (where you can also control whether
the statement is committed or rolled back). Below is one reason to avoid EM
as an editing tool.
http://www.codecomments.com/archive352-2005-10-668434.html

Lastly, the ddl you posted for your view did not include a column named
dscr. There appears to be more to this than you posted.

19 Dec 2005 11:27 AM

mfan

A more complete description of the view (I indeed forgot the 'dscr'
column):

select
pk,
name, dscr,
(   select count(*)
      from dbo.Orders ord
      where ord.cust_fk = cust.pk
   ) as order_count
from dbo.Customer cust

ÎŸ/Î— Scott Morris ÎÎ³ÏÎ±ÏˆÎµ:

> First, there are certain restrictions that must be met before a view is
> considered updateable. Please review the documentation to verify that you
> understand these restrictions and that your view meets these requirements.
> Hint - it does not

Well, documentation says:
doc> No aggregate functions (AVG, COUNT, [...] clauses are used in the
select list.

So, my view is NOT updatable (at least with the previous syntax). Ok.

But, wait... what does this mean:

doc> However, aggregate functions can be used within a subquery defined
in the FROM
doc> clause provided that the derived values generated by the aggregate
functions are
doc> not modified.

That confused me. 3 lines above, I was thinking "there is no way to
include a count column to the view, keeping it updatable".

> (at least based on the DDL you included - which I suspect
> may not be complete).

See above.

> Second, EM is not a good editing tool as it has it own quirks, bugs and
> issues with respect to how it works. If you want unambiguous determination
> of whether your view is updateable or not, issue the insert/update/delete
> statement within QA (or a similar tool) (where you can also control whether
> the statement is committed or rolled back). Below is one reason to avoid EM
> as an editing tool.
> http://www.codecomments.com/archive352-2005-10-668434.html

Ok. You are right.

20 Dec 2005 11:46 PM

Hugo Kornelis

On 19 Dec 2005 03:27:40 -0800, mfan wrote:

(snip)
Show quote

>Well, documentation says:
>doc> No aggregate functions (AVG, COUNT, [...] clauses are used in the
>select list.
>
>So, my view is NOT updatable (at least with the previous syntax). Ok.
>
>But, wait... what does this mean:
>
>doc> However, aggregate functions can be used within a subquery defined
>in the FROM
>doc> clause provided that the derived values generated by the aggregate
>functions are
>doc> not modified.
>
>That confused me. 3 lines above, I was thinking "there is no way to
>include a count column to the view, keeping it updatable".

Hi mfan,

I agree that this is not very clear. I checked the corresponding
description in Books Online for SQL Server 2005, which is at:

http://msdn2.microsoft.com/en-us/library/ms187956(en-US,SQL.90).aspx

(note - Agent thinks the URL stops before the parenthesis, but it
doesn't; the URL ends with .aspx).

Though there's been a major improvement, I still feel that this subject
is still in dire need of better explanation and more examples.

Anyway, what I *think* it means is that there can not be any aggregate
functions in the SELECT list, though there can be aggregates in the FROM
and WHERE clause, provided they don't impact SQL Server's ability to
link one row in the view to exactly one row in one of the base tables.

I didn't test it, but I think queries like the two below would yield an
updatable view:

SELECT       a.Col1, a.Col2
FROM         Table1 AS a
WHERE EXISTS
(SELECT     *
FROM       Table2 AS b
GROUP BY   b.Col3
HAVING     MAX(b.Col4) = a.Col5)

SELECT       a.Col1, a.Col2
FROM         Table1 AS a
INNER JOIN (SELECT   Col3, MAX(Col4)
             FROM     Table2
             GROUP BY Col3) AS b (Col3, MaxCol4)
      ON     b.MaxCol4 = a.Col5

Best, Hugo

--

(Remove _NO_ and _SPAM_ to get my e-mail address)

21 Dec 2005 5:32 AM

Steve Kass

Your view cannot be updated in SQL Server 2000, but it turns
out that it can be updated in SQL Server 2005.

If I run the script below in SQL Server 2000, I get this error message:

Server: Msg 4421, Level 16, State 1, Line 1
Derived table 'V' is not updatable because a column of the derived table
is derived or constant.

If I run the script in SQL Server 2005, however, the underlying table is
correctly updated. The error only arises if I try to update the derived
column order_count, which of course should be impossible.

I suspect the behavior in SQL Server 2000 is incorrect,
and that the view should be updateable in both versions,
and the problem was fixed for 2005.

(I ran the script in Query Analyzer against both versions. I didn't try
Enterprise Manager.)

use tempdb
go

select
OrderID as pk,
ShipVia as cust_fk
into Orders
from Northwind..Orders
go

select
EmployeeID as pk,
ReportsTo as cust_fk,
LastName as name, FirstName as dscr
into Customer
from Northwind..Employees
go

create view V as
select
pk,
name, dscr,
(   select count(*)
      from dbo.Orders ord
      where ord.cust_fk = cust.pk
   ) as order_count
from dbo.Customer cust
go

select * from V
where pk = 2
go

update V set
dscr = 'abc'
where pk = 2
go

select * from V
where pk = 2
go

drop view V
go

drop table tempdb..Orders, tempdb..Customer
go

-- Steve Kass
-- Drew University

mfan wrote:

Show quote

>A more complete description of the view (I indeed forgot the 'dscr'
>column):
>
>select
> pk,
> name, dscr,
> (   select count(*)
>      from dbo.Orders ord
>      where ord.cust_fk = cust.pk
>   ) as order_count
>from dbo.Customer cust
>
>ÎŸ/Î— Scott Morris ÎÎ³ÏÎ±ÏˆÎµ:
>
>
>>First, there are certain restrictions that must be met before a view is
>>considered updateable. Please review the documentation to verify that you
>>understand these restrictions and that your view meets these requirements.
>>Hint - it does not
>>
>>
>
>Well, documentation says:
>doc> No aggregate functions (AVG, COUNT, [...] clauses are used in the
>select list.
>
>So, my view is NOT updatable (at least with the previous syntax). Ok.
>
>But, wait... what does this mean:
>
>doc> However, aggregate functions can be used within a subquery defined
>in the FROM
>doc> clause provided that the derived values generated by the aggregate
>functions are
>doc> not modified.
>
>That confused me. 3 lines above, I was thinking "there is no way to
>include a count column to the view, keeping it updatable".
>
>
>
>>(at least based on the DDL you included - which I suspect
>>may not be complete).
>>
>>
>
>See above.
>
>
>
>>Second, EM is not a good editing tool as it has it own quirks, bugs and
>>issues with respect to how it works. If you want unambiguous determination
>>of whether your view is updateable or not, issue the insert/update/delete
>>statement within QA (or a similar tool) (where you can also control whether
>>the statement is committed or rolled back). Below is one reason to avoid EM
>>as an editing tool.
>>http://www.codecomments.com/archive352-2005-10-668434.html
>>
>>
>
>Ok. You are right.
>
>
>