CODEWITHSUNDEEP
sqloracle-database
Michael Carman
Michael Carman

Reputation: 30831

SELECT query failing with ORA-01722 (invalid number) for valid numbers

I have a table which holds configuration options. The values are stored as strings but can represent different types of data. The TYPE column identifies the data type of the value. (One of STRING, INTEGER, FLOAT, or BOOLEAN).

CREATE TABLE CONFIG (
    NAME  VARCHAR2(256) NOT NULL,
    TYPE  VARCHAR2(20)  NOT NULL,
    VALUE VARCHAR2(1024)
)

I'm trying to run a query that includes a numeric comparison, e.g.

SELECT NAME, VALUE FROM CONFIG
WHERE TYPE = 'INTEGER' AND to_number(VALUE) > 100

but my query fails with "ORA-01722: invalid number". None of the values (for records with type INTEGER) contain invalid characters. Why am I getting an "invalid number" error and how can I fix my query?

Upvotes: 2

Views: 42278

Answers (2)

Prem
Prem

Reputation: 11

You are getting error because to store a number you used a string instead of using a number.To fix your query convert the string to number in the DECODE and then use THAT result in the predicate(Condition in WHERE clause).

SELECT NAME,
  VALUE
FROM CONFIG
WHERE TYPE  = 'INTEGER'
AND DECODE ( (REPLACE(TRANSLATE(TRIM(VALUE),'0123456789','00000000000'),'0' ,NULL)), NULL, to_number(trim(VALUE)) ) > 100;

For more details,refer the link where someone has asked similar question: https://asktom.oracle.com/pls/apex/f?p=100:11:0::::p11_question_id:11504677087008

Upvotes: 0

Michael Carman
Michael Carman

Reputation: 30831

SQL doesn't define the order in which predicates (the conditions of a WHERE clause) are evaluated. Whether or not a query like this "works" depends on the execution plan selected by the server. Changes to your query that affect the plan could cause this error to appear and disappear with no apparent reason.

The best solution is to not store numeric data in string (VARCHAR) fields.

Assuming that isn't an option you need to protect the call to to_number. You can do that with a CASE clause.

SELECT NAME, VALUE FROM CONFIG
WHERE (
  CASE TYPE WHEN 'INTEGER'
  THEN to_number(VALUE)
  ELSE NULL
  END) > 100

Upvotes: 6

Related Questions