Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1177
  • Last Modified:

ORA-01722: invalid number

Hello,

How can I resolve this problem?

SQL> select to_char(DAT,'YYYY MON DD HH24:MI:SS') datetime, sum(to_number(R_RPS)) R_RPS, sum(to_number(W_WPS)) W_WPS,
  2    sum(to_number(Q_SQFULL)) SQLFULL
  3  from TMP__IO
  4  group by to_char(DAT,'YYYY MON DD HH24:MI:SS') ;
select to_char(DAT,'YYYY MON DD HH24:MI:SS') datetime, sum(to_number(R_RPS)) R_RPS, sum(to_number(W_WPS)) W_WPS,
                                                                                                         *
ERREUR Ó la ligne 1 :
ORA-01722: invalid number

Thanks

bibi
0
bibi92
Asked:
bibi92
  • 5
  • 3
  • 3
1 Solution
 
enachemcCommented:
make sure that all the values in that column are realy number (no spaces and no other characters)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I cnform that at least 1 row has data that is not "to_number" ...

I use this function to "help" me find the culprit:
create or replace
FUNCTION GetNumber2(txt_IN IN varchar2)
RETURN NUMBER
IS
	test NUMBER;
	res VARCHAR2(128);
BEGIN

BEGIN
  res := TRANSLATE ( txt_IN, '0123456789' , '          ');
  res := REPLACE( TRANSLATE ( txt_IN, res ,  ' ' ) , ' ', '');

  test := TO_NUMBER(res);

EXCEPTION
  WHEN VALUE_ERROR THEN test:=NULL;
END;

RETURN (test);

END GetNumber2;

Open in new window

0
 
bibi92Author Commented:
values in the columns are varchar.

Thanks

bibi
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
enachemcCommented:
varchar yes, but they should contain only numbers stored as strings
0
 
bibi92Author Commented:
I have created the function but I have the same error.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
using the function, you can find the problem:

select * from yourtable
where GetNumber2(yourfield) <> yourfield

Open in new window

0
 
bibi92Author Commented:
SQL> select * from TMP__IO

  2  where GetNumber2(R_RPS) <> R_RPS
  3  /
where GetNumber2(R_RPS) <> R_RPS
                           *
ERREUR Ó la ligne 2 :
ORA-01722: invalid number

Thanks

bibi
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry:
select * from TMP__IO
  where GetNumber2(R_RPS) IS NULL

Open in new window

0
 
enachemcCommented:
select * from TMP__IO where not regexp_like(R_RPS, '[[:digit:]]+')
0
 
bibi92Author Commented:

SQL> select * from TMP__IO

  2  where GetNumber2(R_RPS) is null
  3  /
no row
0
 
bibi92Author Commented:
Thanks bibi
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 5
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now