CODEWITHSUNDEEP
sqloracle-database
JaviAbos
JaviAbos

Reputation: 3

ORA-01722: invalid number where clause

when I want to compare these two columns it shows me this menssage, without the "where clause" there isnt any error, but the error shows up when I write the "Where"

This is my sql sentences:

select MONEY
       , to_char(MONEY, 'FM999G999G999G999D99', 'NLS_NUMERIC_CHARACTERS='',.''')  
from ACOUNT
where MONEY is not null

menssage error: ORA-01722: invalid number 01722. 00000 - "invalid number" *Cause: The specified number was invalid. *Action: Specify a valid number.

Upvotes: 0

Views: 1394

Answers (2)

ekochergin
ekochergin

Reputation: 4129

It might be not only non-numeric character, but just a comma separated "numeric"-string value

this example causes the same error

select to_char('2,5', 'FM999G999G999G999D99', 'NLS_NUMERIC_CHARACTERS='',.''') from dual;

So, here is the query to find problem records in the ACOUNT table.

select * from ACOUNT where not regexp_like(replace(MONEY, '.', ''),'^[0-9]+$');
-- replace() is here to make regexp_like to ignore values like '2.5' which can be converted to a number

Upvotes: 0

VBoka
VBoka

Reputation: 9083

You have data in your table ACOUNT in the column MONEY that is not numeric.

Here is a demo:

DEMO

I assume that the reason why it is working when there is no where clause is because your tool is showing you only first 50 results... And when you add where clause and filter the data, then in your first 50 results there is a data that is not numeric.

You can change this in your tool by going to : Tools >> Preferences >> Database >> Advanced.

You can create this function:

CREATE OR REPLACE FUNCTION is_number (p_str IN VARCHAR2)
  RETURN NUMBER
IS
  l_num NUMBER;
BEGIN
  l_num := to_number(p_str);
  RETURN l_num;

EXCEPTION
  WHEN others THEN
    RETURN NULL;
END is_number;
/

That I have founded here: How does numeric comparison on Oracle VARCHAR column work?

And then with it you can use this code:

select *
from (select MONEY_c
             , to_char(is_number(MONEY_c), 'FM999G999G999G999D99', 'NLS_NUMERIC_CHARACTERS='',.''')  as money_2
      from ACOUNT)
where money_2 is not null

Upvotes: 1

Related Questions