Hi

my table IVITEMLOC data structure is like this

IVITEMID VARCHAR(30) Not Null
IVLOCID INTEGER Not Null
QTYCOMSALES (DQTY) NUMERIC(18, 4) Nullable
QTYCOMPRDN (DQTY) NUMERIC(18, 4) Nullable
QTYCOMTRANSFER (DQTY) NUMERIC(18, 4) Nullable
QTYEXPPUR (DQTY) NUMERIC(18, 4) Nullable
QTYEXPPRDN (DQTY) NUMERIC(18, 4) Nullable
QTYEXPTRANSFER (DQTY) NUMERIC(18, 4) Nullable
QTYONHAND (DQTY) NUMERIC(18, 4) Nullable
AVERAGECOST (DMONEY) NUMERIC(18, 4) Not Null

the query that i am getting exception is

Update IVITEMLOC
set QTYONHAND=QTYONHAND+-23.0,
averagecost = (((23.00000000) +(qtyonhand * averagecost)) / (23.0+
qtyonhand))
where ivitemid ='RBDPOLN' and ivlocid =1


yes exception is coming for the AVERAGECOST field only but i am wondering
why it works when i excute the same query like this

Update IVITEMLOC
set averagecost = (((23.00000000) +(qtyonhand * averagecost)) /
(23.0+qtyonhand))
where ivitemid ='RBDPOLN' and ivlocid =1

Thanks
usha

----- Original Message -----
From: "Helen Borrie" <helebor@...>
To: <ib-support@yahoogroups.com>
Sent: Tuesday, April 15, 2003 5:32 PM
Subject: Re: [ib-support] arithmetic exception, numeric overflow and string
truncation


> At 03:58 PM 15/04/2003 +0800, you wrote:
> >Hi
> >
> >i am getting the following exception when i execute the following UPDATE
> >statement
> >
> >Update IVITEMLOC
> >set QTYONHAND=QTYONHAND+-23.0,
> >averagecost = (((23.00000000) +(qtyonhand * averagecost)) / (23.0+
qtyonhand))
> >where ivitemid ='RBDPOLN' and ivlocid =1
> >;
> >
> >
> >Statement failed, SQLCODE = -802
> >
> >arithmetic exception, numeric overflow, or string truncation
> >
> >but when i remove the "QTYONHAND in the update then i have no problem.
can
> >any body tell me what is the problem that causing the first query.
> >
> >Update IVITEMLOC
> >set averagecost = (((23.00000000) +(qtyonhand * averagecost)) / (23.0+
> >qtyonhand))
> >where ivitemid ='RBDPOLN' and ivlocid =1
>
> You don't say what data types you are using but my guess is that you are
> getting the middle one - numeric overflow - by having two many places of
> precision in your output values.
>
> heLen
>
>
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>