Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 6 of 6 total |
Computed field from another table |
Wed, Feb 8 2017 3:19 PM | Permanent Link |
Mike | 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. |
Wed, Feb 8 2017 3:30 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Wed, Feb 8 2017 3:42 PM | Permanent Link |
Mike | Thank you Tim.
I will try the query approach. |
Wed, Feb 8 2017 4:46 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Thu, Feb 9 2017 1:33 PM | Permanent Link |
Mike | I understand
|
Tue, Feb 21 2017 9:38 AM | Permanent Link |
Adam Brett Orixa Systems | 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 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |