Re: SQL Error - Data Type Has Changed?

["C. Duncan Hudson" <c_duncan_hudson@YAHOO.COM>]

> The record your program is bombing on may have a
> null value in the linecost field.  This may cause it
> to bomb when multiplied by 100.  I believe there is
> a null value function in Oracle which you could use
> to convert the null to 0 before multiplying by 100.

I looked into the NVL function, unfortunately it expects a string
value and linecost is a not null number.  The table, if it sheds any
light on things, looks like this:

SQL> describe invoicecost;
 Name                            Null?    Type
 ------------------------------- -------- ----
 INVOICENUM                      NOT NULL VARCHAR2(8)
 VENDOR                          NOT NULL VARCHAR2(8)
 INVOICELINENUM                  NOT NULL NUMBER
 GLDEBITACCT                              VARCHAR2(31)
 GLCREDITACCT                             VARCHAR2(31)
 WONUM                                    VARCHAR2(10)
 WPOPERATION                              NUMBER
 EQNUM                                    VARCHAR2(8)
 UNITCOST                                 NUMBER(10,2)
 LINECOST                        NOT NULL NUMBER(10,2)
 PERCENTAGE                      NOT NULL NUMBER(5,2)
 MEMO                                     VARCHAR2(25)
 COSTLINENUM                     NOT NULL NUMBER
 CHARGESTORE                     NOT NULL VARCHAR2(1)
 ICT1                                     VARCHAR2(1)
 ICT2                                     VARCHAR2(1)
 ICT3                                     VARCHAR2(1)
 QUANTITY                                 NUMBER(10,2)
 LOCATION                                 VARCHAR2(8)
 ROWSTAMP                        NOT NULL VARCHAR2(40)


Also, the following select result shows the record preceding the error
(invoicenum = 1) and the record that causes the error (invoicenum =
1011):

SQL> select invoicenum, linecost, percentage, gldebitacct, glcreditacct
  2    from maxdemo.invoicecost
  3   where invoicenum in ('1', '1011');

INVOICEN  LINECOST PERCENTAGE GLDEBITACCT GLCREDITACCT
-------- --------- ---------- ----------- ------------
1011           109        100 6600800800  6800920600
1              900        100 6210300350  6800930600

SQL>

The data looks pretty simiar to me - I'm not sure why SQR is
complaining that the data type has changed.  Anyone have any more
ideas?  Thanks in advance,


Dunc.


_________________________________________________________
DO YOU YAHOO!?
Get your free @yahoo.com address at http://mail.yahoo.com