At 06:24 PM 23/04/2003 +0000, you wrote:

>I am trying to create a procedure that will update the sum totals for
>an invoice by getting the sum of the invoice detail rows for that
>invoice.
>My procedure is:
>CREATE PROCEDURE UPDATE_INVOICE_TOTALS (INVOICE_NO INTEGER) AS
>DECLARE VARIABLE TPRICE NUMERIC(14,2);
>DECLARE VARIABLE TCOST NUMERIC(14,2);
>BEGIN
> SELECT SUM(TOTAL_PRICE),SUM(TOTAL_COST) FROM INVOICE_DETAIL WHERE
>INVOICE_NUM = :INVOICE_NO INTO :TPRICE,:TCOST;
> IF (TPRICE IS NULL) THEN TPRICE=0;
> IF (TCOST IS NULL) THEN TCOST=0;
> UPDATE INVOICE SET SUBTOTAL_AMT = :TPRICE,TOTAL_COST
>= :TCOST,TOTAL_AMT=(:TPRICE+SHIPPING_AMT+TAX_AMT) WHERE INVOICE_NUM
>= :INVOICE_NO;
> SUSPEND;
>END;
>This works most of the time, but sometimes I get a seemingly random
>TOTAL_AMT. Can anyone comment on why that happens or what I am doing
>wrong? I appreciate all help. Thanks.
>
>To give more info, what I do is run this procedure in the application
>code after the user updates, inserts or deletes a row in the
>INVOICE_DETAIL table for the invoice they are working on. I then
>refresh the displayed invoice totals to the user and sometimes the
>TOTAL_AMT is incorrect.
>
>Here is the schema in case that helps.
>
>create domain T_MONEY_TYPE as NUMERIC(14,2) default 0 not null;
>
>create table INVOICE (
>INVOICE_NUM INTEGER not null,
>CREATE_TS TIMESTAMP default
>CURRENT_TIMESTAMP not null,
>USER_ID VARCHAR(15) not null,
>INVOICE_DATE DATE not null,
>CUSTOMER_ID VARCHAR(8) not null,
>PAYMENT_TYPE VARCHAR(12) not null,
>SUBTOTAL_AMT T_MONEY_TYPE not null,
>SHIPPING_AMT T_MONEY_TYPE not null,
>TAX_AMT T_MONEY_TYPE not null,
>TOTAL_AMT T_MONEY_TYPE not null,
>TOTAL_COST T_MONEY_TYPE not null,
>SALESPERSON_ID VARCHAR(6),
>END_DATE DATE,
>PAYMENT_DATE DATE,
>SHIPPED_DATE DATE,
>PAID T_BOOLEAN_TYPE not null,
>PRICING_CODE CHAR(2) not null,
>TOTAL_WEIGHT NUMERIC(7,2) default 0 not
>null,
>PACKAGED_TS TIMESTAMP,
>PACKAGED_BY_USER_ID VARCHAR(15),
>RMA VARCHAR(8),
>NOTE VARCHAR(254),
>RMA_NOTE VARCHAR(254),
>constraint INVOICE_PK primary key (INVOICE_NUM)
>);
>create table INVOICE_DETAIL (
>INVOICE_NUM INTEGER not null,
>STOCK_NUM VARCHAR(12) not null,
>QUANTITY NUMERIC(7) default 0 not
>null,
>DESCRIPTION VARCHAR(50),
>PRICE_EACH T_MONEY_TYPE not null,
>COST_EACH T_MONEY_TYPE not null,
>TOTAL_PRICE T_MONEY_TYPE not null,
>TOTAL_COST T_MONEY_TYPE not null,
>INFO VARCHAR(12),
>PURCHASE_ORDER_ID INTEGER,
>QUANTITY_SHIPPED NUMERIC(7) default 0 not
>null,
>constraint INVOICE_DETAIL_PK primary key (INVOICE_NUM, STOCK_NUM)
>);

The procedure itself looks OK except for two things (see notes 1) and 2)
below) but I think there is potential for the *detail data* which you are
using in the calculation to be not as you assume it is.

Your procedure is referring to the columns TOTAL_PRICE and TOTAL_COST
which, presumably, have been previously calculated by multiplying
PRICE_EACH and COST_EACH by QUANTITY_SHIPPED. The column QUANTITY_SHIPPED
is not defined correctly: here is your DDL:

create table INVOICE_DETAIL (
INVOICE_NUM INTEGER not null,
STOCK_NUM VARCHAR(12) not null,
QUANTITY NUMERIC(7) default 0 not null,
...
QUANTITY_SHIPPED NUMERIC(7) default 0 not null,
...

For a NUMERIC type you have to define two parameters, one for scale and one
for precision. So, for example, if you distribute piece goods (goods sold
by part units) you might define these columns as NUMERIC(7,2). I'm not
sure how the precision of NUMERIC(7) would be stored, but it is probably
being stored as 0 - it's anyone's guess really (and the DDL parser should
forbid it...) And one could only guess at what happens to such numbers
during a multiplication. I think I would want to *begin* by fixing up
these columns - make them either properly defined numeric (if you need
piece goods handling) or Integer (if you never ship part-units) - but I
couldn't guarantee that you don't have corrupt values already in your data.

Notes about the stored procedure:
1) Don't use SUSPEND in loops that don't return output to the client.
2) You don't need to test NOT NULL return values for null, so these lines
will use processing cycles unnecessarily:
IF (TPRICE IS NULL) THEN TPRICE=0;
IF (TCOST IS NULL) THEN TCOST=0;

heLen