September 16, 2018 at 6:22 am
I have this View:
Create View xyz As
Select Value,
MAX(Case When Other = 'Color' Then Description Else '' End) Color,
MAX(Case When Other = 'Font' Then Description Else '' End) Font
From Attributes
Where Value in ('Meeting Room Now - Room No')
group by Value
From this Table :
| Name | Description | Value | Other |
| Meeting Room Now - Room No Color | #8785654 | Meeting Room Now - Room No | Color |
| Meeting Room Now - Room No Font | Arial | Meeting Room Now - Room No | Font |
the problem that this view when i update it return Error :
failed because it contains a derived or constant field.
How to solve it
September 16, 2018 at 10:20 am
--Edit: Should have read the question properly, apologies.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 17, 2018 at 9:39 am
ikrami2000 - Sunday, September 16, 2018 6:22 AMI have this View:
Create View xyz As
Select Value,
MAX(Case When Other = 'Color' Then Description Else '' End) Color,
MAX(Case When Other = 'Font' Then Description Else '' End) FontFrom Attributes
Where Value in ('Meeting Room Now - Room No')
group by Value
From this Table :
Name Description Value Other Meeting Room Now - Room No Color #8785654 Meeting Room Now - Room No Color Meeting Room Now - Room No Font Arial Meeting Room Now - Room No Font the problem that this view when i update it return Error :
failed because it contains a derived or constant field.
How to solve it
You'll either need to use an INSTEAD OF trigger or do this through some other method.
Your view contains aggregates and derives a value from potentially multiple rows. In doing so, you lose the information about the source rows, so you can't use the view as the target in an UPDATE statement, because there is no way to determine which of the rows to update.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 17, 2018 at 11:17 am
ikrami2000 - Sunday, September 16, 2018 6:22 AMI have this View:
Create View xyz As
Select Value,
MAX(Case When Other = 'Color' Then Description Else '' End) Color,
MAX(Case When Other = 'Font' Then Description Else '' End) FontFrom Attributes
Where Value in ('Meeting Room Now - Room No')
group by Value
From this Table :
Name Description Value Other Meeting Room Now - Room No Color #8785654 Meeting Room Now - Room No Color Meeting Room Now - Room No Font Arial Meeting Room Now - Room No Font the problem that this view when i update it return Error :
failed because it contains a derived or constant field.
How to solve it
You can't update such a view. It has aggregates, and that means that there's no way for SQL Server to tie a row in the view to a single specific row in the source table. Any such view would NOT be able to be the target of an UPDATE statement. You would have to update the base table instead. The question then, of course, would be which exact rows would need updating.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 17, 2018 at 12:01 pm
sgmunson - Monday, September 17, 2018 11:17 AMikrami2000 - Sunday, September 16, 2018 6:22 AMI have this View:
Create View xyz As
Select Value,
MAX(Case When Other = 'Color' Then Description Else '' End) Color,
MAX(Case When Other = 'Font' Then Description Else '' End) FontFrom Attributes
Where Value in ('Meeting Room Now - Room No')
group by Value
From this Table :
Name Description Value Other Meeting Room Now - Room No Color #8785654 Meeting Room Now - Room No Color Meeting Room Now - Room No Font Arial Meeting Room Now - Room No Font the problem that this view when i update it return Error :
failed because it contains a derived or constant field.
How to solve it
You can't update such a view. It has aggregates, and that means that there's no way for SQL Server to tie a row in the view to a single specific row in the source table. Any such view would NOT be able to be the target of an UPDATE statement. You would have to update the base table instead. The question then, of course, would be which exact rows would need updating.
I'm guessing that this is an EAV table, so there should only be one unique row to update. If that is the CASE then an INSTEAD OF trigger with an UNPIVOT might be the best option.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 17, 2018 at 1:01 pm
drew.allen - Monday, September 17, 2018 12:01 PMI'm guessing that this is an EAV table, so there should only be one unique row to update. If that is the CASE then an INSTEAD OF trigger with an UNPIVOT might be the best option.Drew
Yeah, that seems likely. I just so HATE any kind of EAV design that I shudder almost every time I encounter one. I do remember encountering something once that actually made sense, but it was NOT pure EAV. More of a hybrid with EAV pieces here and there, and very well thought out...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 20, 2018 at 9:12 am
sgmunson - Monday, September 17, 2018 11:17 AMikrami2000 - Sunday, September 16, 2018 6:22 AMI have this View:
Create View xyz As
Select Value,
MAX(Case When Other = 'Color' Then Description Else '' End) Color,
MAX(Case When Other = 'Font' Then Description Else '' End) FontFrom Attributes
Where Value in ('Meeting Room Now - Room No')
group by Value
From this Table :
Name Description Value Other Meeting Room Now - Room No Color #8785654 Meeting Room Now - Room No Color Meeting Room Now - Room No Font Arial Meeting Room Now - Room No Font the problem that this view when i update it return Error :
failed because it contains a derived or constant field.
How to solve it
You can't update such a view. It has aggregates, and that means that there's no way for SQL Server to tie a row in the view to a single specific row in the source table. Any such view would NOT be able to be the target of an UPDATE statement. You would have to update the base table instead. The question then, of course, would be which exact rows would need updating.
Just tested a similar view without aggregates, and turns out that a case statement similar to OP's seems to be a possible culprit, giving the same error message (in my test anyways). This makes sense as the case statement could be the "derived column" they're talking about.
2 cents!
edit: this does NOT contradict sgmunson's correct post, just mentioning another way a view might not be updatable.
September 20, 2018 at 9:18 am
Of course, it may help to also see the update statement.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply