Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01722: invalid number (oracle 10)
ORA-01722: invalid number [message #421807] Thu, 10 September 2009 13:41 Go to next message
vzeus56
Messages: 2
Registered: September 2009
Location: NJ
Junior Member
Hi,

I'm working on a query that parses a string such as the following, and then sums up the individual parsed values:
0,0,0,0,0,2,0,0,0,0,0,0,0,0,0,0,2,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0

My query is pretty big, so I've reduced it to the following for simplicity:

----------------------
select
SUM(EOC) eoc
FROM (
select
NOTES_TXT,
substr(NOTES_TXT, 1, instr(NOTES_TXT, ',', 1, 1) - 1) AS EOC
from (
select
NOTES_TXT
from
db0dlog0.CALL_APP
WHERE
APP_ID = 'ps' and
NOTES_TXT is not null
)
)
----------------------

When I run it, I get the "ORA-01722: invalid number" error.

However, the following sub-query by itself returns the expected values:

----------------------
select
NOTES_TXT,
substr(NOTES_TXT, 1, instr(NOTES_TXT, ',', 1, 1) - 1) AS EOC
from (
select
NOTES_TXT
from
db0dlog0.CALL_APP
WHERE
APP_ID = 'ps' and
NOTES_TXT is not null
)

Output Shows:

NOTES_TXT =
0,0,0,0,0,2,0,0,0,0,0,0,0,0,0,0,2,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0

EOC = 0
----------------------

I've tried using the TO_NUMBER and CAST functions to try to convert the string value "0" to a numeric one as shown below, but I still get the same error.

----------------------
select
SUM(EOC) eoc -- Also tried SUM(TO_NUMBER(EOC)) EOC HERE!!!
FROM (
select
NOTES_TXT,
TO_NUMBER(substr(NOTES_TXT, 1, instr(NOTES_TXT, ',', 1, 1) - 1)) AS EOC
from (
select
NOTES_TXT
from
db0dlog0.CALL_APP
WHERE
APP_ID = 'ps' and
NOTES_TXT is not null
)
)
----------------------

Anyone have any pointers? I'm sure I'm doing something wrong here, but I'm more of an application develper... my SQL / oracle skills are still pretty basic...


Re: ORA-01722: invalid number [message #421811 is a reply to message #421807] Thu, 10 September 2009 14:06 Go to previous messageGo to next message
vzeus56
Messages: 2
Registered: September 2009
Location: NJ
Junior Member
Never mind everyone... turns out my original query is correct. But I found some non-numeric values in the result. Once I added some additional filters in the WHERE clause it worked like a charm. Cool
Re: ORA-01722: invalid number [message #421812 is a reply to message #421807] Thu, 10 September 2009 14:07 Go to previous message
Michel Cadot
Messages: 63812
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

http://www.orafaq.com/forum/m/255052/102589/?srch=instr+list#msg_255052

Regards
Michel
Previous Topic: ORA-01555 INSERT INTO SELECT
Next Topic: DDL trigger wont fire on ALTER TABLE EXCHANGE PARTITION
Goto Forum:
  


Current Time: Fri Sep 30 07:16:38 CDT 2016

Total time taken to generate the page: 0.07710 seconds