asked on

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

enachemc

make sure that all the values in that column are realy number (no spaces and no other characters)

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bibi92

ASKER

values in the columns are varchar.

Thanks

bibi

enachemc

varchar yes, but they should contain only numbers stored as strings

bibi92

ASKER

I have created the function but I have the same error.

Guy Hengel [angelIII / a3]

using the function, you can find the problem:

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

Open in new window

bibi92

ASKER

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

Guy Hengel [angelIII / a3]

sorry:

select * from TMP__IO
  where GetNumber2(R_RPS) IS NULL

Open in new window

enachemc

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

bibi92

ASKER

SQL> select * from TMP__IO

2 where GetNumber2(R_RPS) is null
3 /
no row

bibi92

ASKER

Thanks bibi