I have a table:

CREATE TABLE "ProductIndirectCosts"
(
"ID" INTEGER DEFAULT UID() NOT NULL,
"ProductsID" INTEGER,
"IndirectCostsID" INTEGER,
"UnitCost" DECIMAL(19,4) GENERATED ALWAYS AS
  IF(Complete = false THEN IndirectUnitCost(IndirectCostsID) ELSE UnitCost),
"QtyUsed" FLOAT DEFAULT 0 NOT NULL,
"TotalCost" FLOAT COMPUTED ALWAYS AS UnitCost * QtyUsed,
"DateCreated" TIMESTAMP DEFAULT Current_Timestamp
)!

Which calls a function:

CREATE FUNCTION "IndirectUnitCost" (IN "aID" INTEGER)
RETURNS DECIMAL(19,4)
BEGIN
 DECLARE Result DECIMAL(19,4);
 DECLARE Crsr CURSOR FOR Stmt;
PREPARE Stmt FROM
' SELECT UnitCost
  FROM IndirectCosts
  WHERE ID = ? ';
OPEN Crsr USING aID;
FETCH FIRST FROM Crsr('UnitCost') INTO Result;
IF Result is NULL THEN
 SET Result = 0;
 END IF;
RETURN Result;

When I run some INSERT SQL:

INSERT INTO ProductIndirectCosts
(ProductsID, IndirectCostsID, QtyUsed)
VALUES
(1000, 11000, 1)!

The resulting row is inserted, but shows a Unit Cost and a TotalCost with NULL value.

When I then "bump" the resulting row (for example by incrementing the DateCreated) the UnitCost and TotalCost both compute.

My understanding is that UnitCost and TotalCost should compute automatically, and that the "bump" should not be necessary. What I am doing wrong?

Adam


Interesting bit of SQL - I would never have thought of doing that. The only thing I can spot that looks suspicious is IF(Complete = false - where does Complete come from and what is its value on insert?

Roy Lambert

Roy

Thanks for the response.

>>Interesting bit of SQL - I would never have thought of doing that.

I use internal functions like this quite a bit, it basically "builds in" quite a bit of the computation of a system, which otherwise has to be written into a Delphi EXE, or returned in Reports via complex SQL.

I also like it because the statement can be conditional (as in this case) so that once a record is "Complete" the computation does NOT re-trigger.

I use it a lot and it works usually! Just in this case the simple INSERT is not resulting in the FUNCTION being triggered ... emoji for head-scratching ...

>>The only thing I can spot that looks suspicious is
>>IF(Complete = false - where does Complete come from
>>and what is its value on insert?

I didn't include the whole table definition to try not to be confusing, but I should have included this part:

"Complete" BOOLEAN DEFAULT False NOT NULL,

"Complete" is a simple BOOLEAN field. However, note that it is false by default. I assumed that would mean that it would compute as false in the INSERT (Likely I am wrong!) I will try to restructure the table and see if that fixes it.

OK: I tried the following:

ALTER TABLE ProductIndirectCosts
ALTER COLUMN "UnitCost" AS DECIMAL(19,4) GENERATED ALWAYS AS IF(Complete = false or Complete is NULL THEN IndirectUnitCost(IndirectCostsID) ELSE UnitCost)

And now it works fine.

That definitely counts as a good bug-spot Roy. Another beer I owe you.

Adam


Tim will know the answer but I'd guess there are two possible causes.

1. Values aren't put into the field until the record is posted
2. Complete is defined after UnitCost and hence even though it has a default it hasn't received that when the generated call is processed


Out of interest since these are processed every time the record is altered (I think) what's the impact on performance? Would triggers be bettert?

Roy Lambert

Roy

>>Out of interest since these are processed every time the record is altered (I think)
>>what's the impact on performance?

These functions are evaluated every time the row is updated, even if the update does not involve the field containing the function. This is fine, but obviously can have performance implications. It all happens on the server-side ... and I always test how long a function takes to run. Simple SUMs on OrderItems for an Order are thousandths of a second ... so you literally don't notice it.

>>Would triggers be bettert?

I prefer having the function directly visible in the row of the table as a GENERATED BY as it is immediately obvious and clear which functions are computing which rows. With one or more Triggers you have the added indirection of having to find and check the contents of the trigger.

Using GENERATED results in a read-only field. This may not work for particular situations (if the user wants to be able to override the value) but is clear. With a trigger I guess that the field might appear to be read / write, but the trigger would over-ride the value on-post, which might be confusing for users.

I don't know whether Trigger would be faster than GENERATED ...

Adam


>These functions are evaluated every time the row is updated, even if the update does not involve the field containing the function. This is fine, but obviously can have performance implications. It all happens on the server-side ... and I always test how long a function takes to run. Simple SUMs on OrderItems for an Order are thousandths of a second ... so you literally don't notice it.

Not unless you're doing some nasty batch works I would guess which is why I asked the question - the end user altering a single row may get a chance to blink but that would be about all

Roy

There is definitely a need for caution. It is possible to write some very slow EDB functions, at which point your POSTs would slow right down, but with care it is super-fast.

The part I like best is that it "bakes in" quite a bit of functionality I used to write in Delphi, meaning that users accessing over ODBC (i.e. in Excel) also see all the computational functionality, and I can extend and tweek it without rebuilding an EXE by altering the DB.