Re: ORA-01722 invalid number

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

last post

Wolfgang Breitling wrote:

| 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

• -- Joseph S Testa Chief Technology Officer Data Management Consulting 614-791-9000 It's all about the "CACHE" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.1 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFAGyRxR8fSap71V7YRApzzAKCoA6lzRXR7hCWkZmSA0RW+DXvg/QCeNQPK WQEp4pYfX7j7JxYW8RMbeN8=
=kTiZ
-----END PGP SIGNATURE-----

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Joe Testa
  INET: jtesta_at_dmc-it.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).