Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01722
ORA-01722 [message #307061] Mon, 17 March 2008 14:27 Go to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
I get the ORA-01722 error when I try computations on a field that I've stripped out all non-numberic characters.

SELECT
   value
FROM
   result
WHERE
   TO_NUMBER(SUBSTR(REPLACE(TRANSLATE(value, 
' abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ/`<>?!@#$%^&*()_+-=', 
'_'),'_',''),1,3)) > 400


However, if I use in a CASE statement I get no errors

SELECT
   CASE
      WHEN TO_NUMBER(SUBSTR(REPLACE(TRANSLATE(value, 
' abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ/`<>?!@#$%^&*()_+-=', 
'_'),'_',''),1,3)) > 400
      THEN value
   END value
FROM
   result


Advice would be greatly appreciated.

[Updated on: Mon, 17 March 2008 14:35] by Moderator

Report message to a moderator

Re: ORA-01722 [message #307062 is a reply to message #307061] Mon, 17 March 2008 14:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oracle version (4 decimals)?
Post values that lead to the error.

What is error ORA-01722? It is not in the documentation.

Regards
Michel

[Updated on: Mon, 17 March 2008 14:38]

Report message to a moderator

Re: ORA-01722 [message #307068 is a reply to message #307062] Mon, 17 March 2008 14:52 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
Oracle version 9.2.0.1.

ORA-01722 is an invalid number error.

I've used this technique many times with alphanumeric fields for computational purposes and have never had a problem.

I've even listed all values with this SELECT statement and all values are valid....numeric

SELECT
   TO_NUMBER(SUBSTR(REPLACE(TRANSLATE(value, 
' abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ/`<>?!@#$%^&*()_+-=', 
'_'),'_',''),1,3))
FROM
   result
Re: ORA-01722 [message #307069 is a reply to message #307068] Mon, 17 March 2008 15:10 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Can you determine the row that's raising the exception?
Is this your complete query? Maybe something in your where-clause that converts to numeric?
Re: ORA-01722 [message #307074 is a reply to message #307069] Mon, 17 March 2008 15:23 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
There's only four records that are returned.

If I run the query one record at a time I get no error.
Re: ORA-01722 [message #307075 is a reply to message #307068] Mon, 17 March 2008 15:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe there is another character you don't translate.
Post a test we can reproduce the same thing.
If we can't reproduce the error, I'm afraid we can't help more.

Regards
Michel
Re: ORA-01722 [message #307076 is a reply to message #307074] Mon, 17 March 2008 15:30 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
Show us the data in the table. And don't just type it in; cut and paste your session.

[edit] fixed typo.

[Updated on: Mon, 17 March 2008 15:30]

Report message to a moderator

Re: ORA-01722 [message #307079 is a reply to message #307074] Mon, 17 March 2008 15:40 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
staann56 wrote on Mon, 17 March 2008 21:23
There's only four records that are returned.

If I run the query one record at a time I get no error.

That's what I meant.
I think there's something in the where-clause that's erroring out, not the to_number in your select-clause.
Re: ORA-01722 [message #307096 is a reply to message #307079] Mon, 17 March 2008 17:23 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
There is no TO_NUMBER in the SELECT clause. The TO_NUMBER function is in the WHERE clause.

What I'm trying to get to work is:

SELECT
   value
FROM
   result
WHERE
   TO_NUMBER(SUBSTR(REPLACE(TRANSLATE(value, 
' abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ/`<>?!@#$%^&*()_+-=', 
'_'),'_',''),1,3)) > 400
Re: ORA-01722 [message #307098 is a reply to message #307096] Mon, 17 March 2008 17:31 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Check the output of the following query.
select SUBSTR(REPLACE(TRANSLATE(value, 
' abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ/`<>?!@#$%^&*()_+-=', 
'_'),'_',''),1,3) from result;

Alternatively write a wrapper around to_number and usage the function in your select statement. By doing it that way you can catch the exception and throw an error along with the record identifier.

Regards

Raj
Re: ORA-01722 [message #307104 is a reply to message #307098] Mon, 17 March 2008 18:07 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
I'm not sure if this will help but....Here's the data

CREATE TABLE RESULT (
   NAME	        VARCHAR(30),
   PID		NUMBER(4),
   FAC		VARCHAR(3),
   VALUE	VARCHAR(6))

INSERT ALL
  INTO RESULT (NAME, PID, FAC, VALUE)
    VALUES (SMITH JOHN, 2309, RMC, 492 H)
  INTO RESULT (NAME, PID, FAC, VALUE)
    VALUES (RODGERS LARRY, 2309, RMC, 560 H)
  INTO RESULT (NAME, PID, FAC, VALUE)
    VALUES (JONES TAMI, 2309, RMC, 702 H)
  INTO RESULT (NAME, PID, FAC, VALUE)
    VALUES (KENNY PAUL, 2309, RMC, <250 H)


The SQL to run against the data:

SELECT
   value
FROM
   result
WHERE
   TO_NUMBER(SUBSTR(REPLACE(TRANSLATE(value, 
' abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ/`<>?!@#$%^&*()_+-=', 
'_'),'_',''),1,3)) > 400


When I run the above SQL I get the ORA-01722 invalid number error.

Any ideas?

Thanks,
Stan
Re: ORA-01722 [message #307168 is a reply to message #307104] Tue, 18 March 2008 01:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select value from result;
VALUE
------
492 H
560 H
702 H
<250 H

4 rows selected.

SQL> SELECT
  2     value
  3  FROM
  4     result
  5  WHERE
  6     TO_NUMBER(SUBSTR(REPLACE(TRANSLATE(value, 
  7  ' abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ/`<>?!@#$%^&*()_+-=', 
  8  '_'),'_',''),1,3)) > 400
  9  /
VALUE
------
492 H
560 H
702 H

3 rows selected.

Check if the data is the one you think it is.
Post the result of:
select value, dump(value) from result;

Regards
Michel
Re: ORA-01722 [message #307180 is a reply to message #307104] Tue, 18 March 2008 01:44 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
SQL> INSERT ALL
  2    INTO RESULT (NAME, PID, FAC, VALUE)
  3      VALUES (SMITH JOHN, 2309, RMC, 492 H)
  4    INTO RESULT (NAME, PID, FAC, VALUE)
  5      VALUES (RODGERS LARRY, 2309, RMC, 560 H)
  6    INTO RESULT (NAME, PID, FAC, VALUE)
  7      VALUES (JONES TAMI, 2309, RMC, 702 H)
  8    INTO RESULT (NAME, PID, FAC, VALUE)
  9      VALUES (KENNY PAUL, 2309, RMC, <250 H);
    VALUES (SMITH JOHN, 2309, RMC, 492 H)
                  *
ERROR at line 3:
ORA-00917: missing comma


staann56 wrote on Tue, 18 March 2008 00:07
When I run the above SQL I get the ORA-01722 invalid number error.


Could you show that please? Is this your data, or is this merely the data you expect to be returned?
Re: ORA-01722 [message #307304 is a reply to message #307180] Tue, 18 March 2008 07:17 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
Guys, I appreciate you all looking at this for me. I think I'm going to try another approach since I can't get this to work.

I'll keep you posted.

Thanks,
Stan
Re: ORA-01722 [message #307310 is a reply to message #307104] Tue, 18 March 2008 07:49 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
staann56 wrote on Mon, 17 March 2008 19:07
I'm not sure if this will help but....Here's the data

CREATE TABLE RESULT (
   NAME	        VARCHAR(30),
   PID		NUMBER(4),
   FAC		VARCHAR(3),
   VALUE	VARCHAR(6))

INSERT ALL
  INTO RESULT (NAME, PID, FAC, VALUE)
    VALUES (SMITH JOHN, 2309, RMC, 492 H)
  INTO RESULT (NAME, PID, FAC, VALUE)
    VALUES (RODGERS LARRY, 2309, RMC, 560 H)
  INTO RESULT (NAME, PID, FAC, VALUE)
    VALUES (JONES TAMI, 2309, RMC, 702 H)
  INTO RESULT (NAME, PID, FAC, VALUE)
    VALUES (KENNY PAUL, 2309, RMC, <250 H)




Of course you know these are ALL invalid SQL statements. You have now been asked three separate times to show us your session of your select statement 9not what you THINK the data is. Why would you expect people to answer your question when you won't do the same for others. Jeez!
Re: ORA-01722 [message #307316 is a reply to message #307304] Tue, 18 March 2008 08:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post the result of the query I gave.

Regards
Michel
Re: ORA-01722 [message #307413 is a reply to message #307180] Tue, 18 March 2008 17:38 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
Frank: Yes this is the data I was expecting in my result set. It's what's in the table.

Michel: I just saw the second part of your post. I'll post the dump.

Joy: Calm down. Don't assume I'm trying to be vague. If you have nothing productive to add, DON'T POST The data is what's in the table so don't presume or assume I'm posting what I THINK the data is.

Give people the benifit of the doubt. I missed your edited post from 03/17/08 16:30 and the second part of Michel's post.

Try being nice!
Re: ORA-01722 [message #307499 is a reply to message #307413] Wed, 19 March 2008 00:47 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
My bet is that your problem lies in the data that is filtered out by one of the where-clauses that you did not show us.
There is (probably) some data that fails a conversion to number that is done in one of the where-clauses, but it is NOT one of these records.
The reason that this behavior could occur 'all of a sudden' is that the optimizer can switch plans.
Re: ORA-01722 [message #307564 is a reply to message #307413] Wed, 19 March 2008 02:31 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
So you are trying to say
select count(*) from result;

The above code will yield only 4 records. Is that correct ?

Regards

Raj
Re: ORA-01722 [message #307661 is a reply to message #307564] Wed, 19 March 2008 06:46 Go to previous message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
Yes that is correct. At the point in time that I first posted there were only four records. I figured out what was going on. I had an additional table joined to the RESULT table that I didn't think would make a difference. The table name is RESULT_CODES that only provides a label name for the value. It was joined by LABEL:

VALUE.LABEL = RESULT_CODES.LABEL

I don't understand why this would make a difference but it does. I guess it's as Frank said, where the optimizer switched plans.?.?

I'm sorry to have wasted everyones time.

Thanks,
Stan
Previous Topic: Sql query or pl/sql program
Next Topic: insert
Goto Forum:
  


Current Time: Thu Dec 08 12:06:37 CST 2016

Total time taken to generate the page: 0.23247 seconds