Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: ORA-01722 invalid number

Re: ORA-01722 invalid number

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Fri, 30 Jan 2004 19:29:35 -0800
Message-ID: <F001.005DE78A.20040130192935@fatcity.com>


My guess would be that company is not a number but because you do compare it to a number "company=2000" Oracle does an implicit conversion "to_number(company)=2000" and that fails when it hits a row where company is not numeric.
If my guess is right try company='2000'

At 07:59 PM 1/30/2004, you wrote:
>I am running a query:
>select....
>from....
>WHERE COMPANY=2000 AND
>LOCATION='CDJ01' AND ITEMLOC.ACTIVE_STATUS='A' AND
>POVAGRMTLN.PROCURE_GROUP='SMAR'
>AND RTRIM(LTRIM(POVAGRMTLN.VEN_AGRMT_REF)) = 'COM1-21-LARROC NANCY'
> AND RTRIM(LTRIM(POVAGRMTLN.VENDOR))='21'
>
>and in the next part of the where I got this error:ORA-01722 invalid number
>
>AND RTRIM(LTRIM(POVAGRMTLN.VEN_AGRMT_REF)) = 'COM1-21-LARROC NANCY'
>
>If I write 'COM-21-LARROC NANCY' the error dissapear, I don't have any
>clue why this happen, I read about this error but the help don't seem to
>fit on this case.
>The POVAGRMTLN.VEN_AGRMT_REF field is char(30).
>
>ORA-01722 invalid number
>
>Cause: The attempted conversion of a character string to a number failed
>because the character string was not a valid numeric literal. Only numeric
>fields or character fields containing numeric data may be used in
>arithmetic functions or expressions. Only numeric fields may be added to
>or subtracted from dates.
>
>Action: Check the character strings in the function or expression. Check
>that they contain only numbers, a sign, a decimal point, and the character
>"E" or "e" and retry the operation.

Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Wolfgang Breitling
  INET: breitliw_at_centrexcc.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Fri Jan 30 2004 - 21:29:35 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US