SQL Related Articles: Adding and using computed fields 2004-03-25 -- Rick Martin Newsgroups: softvelocity.clarion.databasedrivers
>On Thu, Mar 25 2004 8:18 am, arnor@icetips.com (Arnor Baldvinsson) said:
>Hi Paul,
>
>Just curious: What exactly do you mean by a computed field? Is it
>something that is calculated in a trigger or sp in the database?
If you don't know computed fields you are in for a treat.
Here is a simple example.
Record
Quantity Decimal(16,4)
UnitPrice Decimal(16,4)
TotalPrice AS (Quantity * UnitPrice)
Now when you select a record from the server the TotalPrice field is returned
just like a regular field with the computed value. You elminate the need to
make the calculation in your own code in all the different places you need the
total price. You can also call user functions to return the value for the
computed field.
A more realistic example: We have a quantity field in one table that can come
from three different places depending on conditions. 1) the user directly
entered the quantity. 2) the user entered the total and the quantity is
calculated by dividing by price. 3) the quantity is a sum of fields from
another table.
So the quantity field in the table is the return value of a user function. This
function tests the conditions and returns the appropriate value for that
record. It elminates a whole bunch of code spread out throughout the program in
windows, reports, etc.
HTH,
Rick Martin
Sharpe Software, Inc
>If you don't know computed fields you are in for a treat.
Thanks - haven't needed one yet, but it sure could come in handy:) In
SQL Anywhere, they have COMPUTE columns, which I guess is the same
thing:
CREATE TABLE product
(
id INTEGER NOT NULL,
JProd asademo.Product NOT NULL,
name CHAR(15) COMPUTE ( JProd>>name ),
PRIMARY KEY ("id")
)
ALTER TABLE product
ADD inventory_value INTEGER
COMPUTE ( JProd.quantity * JProd.unit_price )
Best regards,
Arnór Baldvinsson
Printed November 21, 2024, 6:50 pm This article has been viewed/printed 35219 times. Google search has resulted in 40 hits on this article since January 25, 2004. |