russel wrote:
I have a stored procedure to calculate an indication of a record's age. It return "over 100 days ago" or "over 3 months ago" or ... Call it GetAgeMsg(myTS). Can I use a stored procedure to calculate a computed field for FB 2.5.x? If so what is the syntax?
Martijn Tonies answers:
Yes, you can use a stored procedure in a computed field --
The routine needs to be selectable, add a SUSPEND for that.
The trick is to use double parenthesis, here's an example, tested in Database Workbench:
SET TERM ^^ ;
CREATE PROCEDURE CALC_ORDER_HISTORY_INDICATOR (
ORDERDATE Date)
returns (
TXT VarChar(100))
AS
declare variable days_since integer;
begin
days_since = current_date - orderdate;
if (days_since > 365)
then txt = 'Over a year ago';
else if (days_since > 180)
then txt = 'Over 6 months ago';
else if (days_since > 32)
then txt = 'Over a month ago';
else if (days_since > 8)
then txt = 'Over a week ago';
else txt = days_since || ' days ago';
suspend;
end ^^
SET TERM ; ^^
CREATE TABLE ORDERS
(
ORDERID INTEGER NOT NULL,
CUSTID INTEGER NOT NULL,
ORDERNR INTEGER,
ORDER_DATE DATE NOT NULL,
EXPECTED_DELIVERY_DATE DATE,
TIME_SINCE_ORDER VARCHAR( 100) COMPUTED BY ( ( select
txt from CALC_ORDER_HISTORY_INDICATOR (order_date)
) ),
CONSTRAINT PK_ORDERS PRIMARY KEY (ORDERID)
);
As you can see, the COMPUTED BY clause has a double set of parenthesis, allowing you to use the procedure.
Thomas Steinmaurer adds:
As Martijn has pointed out, it is definitely possible.
But to be on the safe side, I would try a backup/restore cycle with gbak to see if that SP dependency in a COMPUTED BY field causes troubles from a dependency POV.
AFAIR, this has been fixed in 2.1, but I would give gbak a test-drive on that, otherwise you might end up with non-working backups.
Just a thought.