A detail table with a quantity is summed for each master record via a COMPUTED
BY field in the master table.

If a SP is used then all records in the master have the same computed sum of
1.0, even though only one of them has a record in the detail table. This is
Option (a) below.

If the direct SELECT statement is used instead of a call to a SP, then all
master totals are NULL except for the one record, which has the correct total
of 1.0.

As I want the SUM to return 0 if there are no detail records, I need to use
the SP approach.

Is what I've done incorrect, or is this a bug?


CREATE TABLE "Feed Disposed"(
"Inventory Location ID BIGINT,
"Quantity Disposed" NUMERIC(9,3)
);

Only one record:
4, 1.000

CREATE TABLE "Feed Inventory Location"(
ID BIGINT,


(* Option (a)

"Total Disposed" COMPUTED BY ((SELECT "Total Disposed"
FROM Get_FIL_TotalDisposed("Feed Inventory
Location".ID))),

(* Option (b)

"Total Disposed" COMPUTED BY ((SELECT SUM (FD."Quantity Disposed")
FROM "Feed Disposed" FD
WHERE FD."Inventory Location ID" = "Feed
Inventory Location".ID
))
*)
);

10 records

With option (a)
1, 1.0
2, 1.0
3, 1.0
4, 1.0
5, 1.0
6, 1.0
7, 1.0
8, 1.0
9, 1.0
10, 1.0

With option (b)
1, NULL
2, NULL
3, NULL
4, 1.000
5, NULL
6, NULL
7, NULL
8, NULL
9, NULL
10, NULL

ALTER PROCEDURE Get_FIL_TotalDisposed(ID BIGINT)
RETURNS ("Total Disposed" NUMERIC(18,3))
AS
BEGIN
SELECT SUM (FD."Quantity Disposed")
FROM "Feed Disposed" FD
WHERE FD."Inventory Location ID" = ID
INTO "Total Disposed";

IF ("Total Disposed" IS NULL) THEN
BEGIN
"Total Disposed" = 0;
END

SUSPEND;
END#


--
Raymond Kennington
Programming Solutions
TeamW2W (InfoPower)