Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: ORA-01722 invalid number
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
![]() |
![]() |