Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to determine if a column of a view is derived or constant?

Let's say I have the following table:

create table t_Item (
    ItemID int not null identity(1,1) constraint PK_Item primary key,
    Description varchar(256) not null,
    Price decimal(10,2) not null
)

and the following view:

create view Item as
  select ItemID
        ,Description
        ,Price
        ,1.09 Tax
        ,Price * 1.09 TaxedPrice
    from t_Item

TaxedPrice is a derived column, and Tax is a constant column.

Therefore, I can't insert or update any of them. The first following query would pass, whereas the other ones would fail with an error.

insert into Item (Description, Price) values ('Test item', 14.00)

insert into Item (Description, Price, TaxedPrice) values ('Test item', 14.00, 15.26)

insert into Item (Description, Price, Tax) values ('Test item', 14.00, 1.09)

And here is the returned error message:

Update or insert of view or function 'Item' failed because it contains a derived or constant field.

Is there a way, maybe with the system views, to list the view columns which must not be updated?

like image 633
madprog Avatar asked Mar 06 '13 14:03

madprog


People also ask

What is a derived or constant field?

A derived field is a simple field or table field column whose value is derived from the value of one or more fields or columns on the same form or a constant. The value is determined by a formula that is specified when the form is defined in VIFRED.

Does view contain derived columns?

View is a virtual table. A view definition is permanently stored as part of the database. View never contains derived columns.

What is a derived column?

Derived columns let you move the processing of an expression from the target instance to the source instance. For example, you may have already defined an expression that concatenates the values of two source columns, FIRSTNAME and LASTNAME, and mapped this expression to a target column named called FULLNAME.

What is a derived column in SSIS?

The Derived Column transformation creates new column values by applying expressions to transformation input columns. An expression can contain any combination of variables, functions, operators, and columns from the transformation input.


1 Answers

Looks like there is no system view saves information you are looking for. You can find out derived column or constant column by parsing the view definition or by exception handling...not good, but didn't find other ways...

like image 190
ljh Avatar answered Sep 29 '22 09:09

ljh