Hi,

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.

Hope this helps.


With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

>Subject: [firebird-support] stored procedure for a computed field?

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?
Many thanks.