CODEWITHSUNDEEP
sqloracle-database
S Roshan
S Roshan

Reputation: 23

DatabaseError: ORA-01722: invalid number

When I run the following query on a postgres database, it works well. But on an oracle database it sends error

ORA-01722: invalid number

This query on oracle had been working till one week ago, and during this week it generates an error.

The general syntax is as below:

SELECT MIN(idd) FROM
(SELECT CAST(id AS INTEGER) idd
 FROM prev_table) new_table

Without the MIN function, it works.

The problem occurs when I use MIN() to get the minimum id.

In this prev_table, id column is defined as varchar, and I want to convert them to integer and then get minimum.

Upvotes: 0

Views: 649

Answers (1)

MT0
MT0

Reputation: 167774

You should not store numbers as text.

From Oracle 12.2, you can use TO_NUMBER(... DEFAULT ... ON CONVERSION ERROR):

SELECT MIN(TO_NUMBER(id DEFAULT NULL ON CONVERSION ERROR)) AS min_id
FROM   prev_table
WHERE  TO_NUMBER(id DEFAULT NULL ON CONVERSION ERROR) IS NOT NULL;

or VALIDATE_CONVERSION:

SELECT MIN(TO_NUMBER(id)) AS min_id
FROM   prev_table
WHERE  VALIDATE_CONVERSION(id AS INTEGER) = 1;

Which, for the sample data:

CREATE TABLE prev_table (id) AS
SELECT 'ABC' FROM DUAL UNION ALL
SELECT '3e2' FROM DUAL UNION ALL
SELECT '1000' FROM DUAL UNION ALL
SELECT '999.9' FROM DUAL UNION ALL
SELECT '+420' FROM DUAL UNION ALL
SELECT '3e4e' FROM DUAL;

Both output:

MIN_ID
300

fiddle

Upvotes: 1

Related Questions