Hi,

Is it possible to link a computed field to another table?

Table Orderlines
============
OrderID
ProductID
ProductDescription (uses ProductID to refer to Products table and get field value Description)

Thanks in advance.

Mike,

<< Is it possible to link a computed field to another table? >>

Not with a computed column.  You can do so with a generated column, but you'll need to create a function that can be called from within the generated column expression:

http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=CREATE_FUNCTION

An easier way to handle this might be to use a special type of query that can return a sensitive result set but still allows for such "lookups":

SELECT CustNo,
(SELECT Company FROM Customer WHERE Customer.CustNo=Orders.CustNo) AS Company
FROM Orders

Just make sure that the TEDBQuery.RequestSensitive property is set to True.

Tim Young
Elevate Software
www.elevatesoft.com

Thank you Tim.

I will try the query approach.

Mike,

BTW, that query example is from the manual, and isn't intended to represent your use case, in case you're wondering why it doesn't match your table/column names.

Tim Young
Elevate Software
www.elevatesoft.com

I understand

Mike

I use table definitions of the type you are planning.

You end up with something like:

ALTER TABLE Products
ALTER COLUMN ProductDescription AS VARCHAR(100) GENERATED ALWAYS AS ProductDescription(ProductID)

Where "ProductDescription" is a user-defined function like this:

CREATE FUNCTION "ProductDescription" (IN "aProductsID" INTEGER)
RETURNS VARCHAR(100)
BEGIN
 DECLARE Crsr CURSOR FOR Stmt;
 DECLARE Result VARCHAR(100);
PREPARE Stmt FROM
' SELECT
   Description
 FROM Products
 WHERE ID = ? ';
OPEN Crsr USING aProductsID;
FETCH FIRST FROM Crsr('Description') INTO RESULT;
RETURN Result;
END

--

All this works hunky dory, but there are a few issues to remember:

1. You should FK on the ProductID to ensure it exists (sure you know this already)

2. You may want to think long and hard about allowing / disallowing NULL values in fields, especially if you start using this method to GENERATE numeric values.

3. You have to add TRIGGERS to the Product table to ensure that when the Description field changes the change "ripples" through from Product to OrderLines (if this is the behaviour you want!).

It is important to understand that GENERATE is called when the row in OrderLines is updated.

Therefore if the Description is updated in the Product table it will not automatically update in OrderLines.

This is useful if you want the row in OrderLines to hold the value of "Description" at the time the OrderLine was created, but it is a problem if you want "ProductDescription" to always be up-to-date.

Depending on your use-case, a TRIGGER can be added to Products to update OrderLines to refresh the ProductDescription.

There is one final alternative!!

Remember that you don't have to include the ProductDescription in the OrderLines table at all. You can call it up using SQL.

I hope this is useful

Adam