Formula
-
When we create a new table from EM there is a Tab called "Formula" for each column. What is the purpose of this field and any small example?
Thanks
Jagan
-
It is to have a calculated column.
Exemple from an inventory table
Columns
Qty decimal (18,4)
Price decimal(18,4)
Value as Qty * Price
-
& here's another that I created "literally" 5 minutes ago...
CREATE TABLE [tblTotalObligations] (
[BudgetFK] [int] NOT NULL ,
[Obligations] [int] NOT NULL CONSTRAINT [DF_tblTotalObligations_Obligations] DEFAULT (0),
[LessReimbursables] [int] NOT NULL CONSTRAINT [DF_tblTotalObligations_LessReimbursables] DEFAULT (0),
[TotalObligations] AS ([Obligations] - [LessReimbursables])
) ON [PRIMARY]
GO
**ASCII stupid question, get a stupid ANSI !!!** -
Normally you would use a view rather than a computed column, except when you want to search based on the value of the computed column...
For example, if you had
Qty, ItemPrice, TotalPrice columns,
you could calculate TotalPrice = ItemPrice * Qty.
If all you wanted was this data in a report or your app, then apart from calculating application side (which is often best if possible), you would calculate it in a view rather than storing the value permanently.
The exception to this is if you wanted to find all TotalPrices that are between $X and $Y. In that case, it is inefficient to perform the multiplication on every row in the table to find the matching rows. Instead, store the TotalPrice column and create an index on it to find the data efficiently.
Additionally, I suppose that if TotalPrice was a value that you were after all the time, and it was expensive to compute (eg, involved some other calculations, perhaps a UDF is involved?) then you would want to store it rather than calculate it each time.
Hope this helps.
Ian
-
Can we use IF or CASE Statements in the Formula?
Jagan
-
Did you try it???
yes you can.
-
yes - here's an example..
CREATE TABLE t2 (a char(1),
b AS CASE a
WHEN 'x' THEN 'true'
ELSE 'false'
END)
**ASCII stupid question, get a stupid ANSI !!!** -
Thanks for the help.
Jagan
-
HTH.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply