Re: ORA-01722: invalid number

ampierce wrote:
> HELP! Okay, I am doing a simple update statement in which I take a
> decimal field and multiply it by an integer field to populate into a
> decimal field. Here is my example:
>
> update tts_hlpdsktkt_acq_quotelines
> set extprice = numberofitems*unitprice
> where ttstktacqquotelineid = '000000000126090'
>
> in my database, the extprice is a decimal field, as well as the
> unitprice, the numberofitems is an integer field.
>
> I only receive the error if unitprice is over 999. Seems like a "," is
> getting put in there somehow and it is causing the statement to error
> out.
>
> Any ideas?

It looks like it should work. If I understand it correctly, what is below shows that it should.

SQL> create table t0922(num number(10,0),price number(10,2),extprice number(10,2));

Table created.

SQL> insert into t0922(num,price) values(100,999.5);

1 row created.

SQL> insert into t0922(num,price) values(100,1000.5);

1 row created.

SQL> update t0922 set extprice=num*price;

2 rows updated.

SQL> select * from t0922;

       NUM PRICE EXTPRICE
---------- ---------- ----------

       100      999.5      99950
       100     1000.5     100050

SQL> Received on Thu Sep 22 2005 - 12:54:02 CDT