CODEWITHSUNDEEP
oracle11g
DenaliHardtail
DenaliHardtail

Reputation: 28336

Why is this SQL returning "Invalid Number" when I convert VARCHAR2 to an INTEGER?

I have this SQL:

SELECT C1,CAST(C1 as NUMBER) FROM my_materialized_view WHERE C1 IS NOT NULL AND C1 != '0'

C1 is a VARCHAR2 column that contains nulls and 0 values. Those are filtered out in the WHERE clause. This query works if I SELECT C1 FROM - remove the CAST() - from the statement.

When I run the above SQL in its entirety, I get ORA-01722: invalid number.

How should I convert a column in the materialized view to a number so I can perform calculations?

UPDATE - I also tried select cast(c1 as integer) as suggested in comments. It returns the same error.

UPDATE - I ran this select C1 from my_materialized_view where C1 IS NOT NULL and C1 != '0' order by C1 and did not find any values outside the range of 1001000 to 999300. Seems like a valid test.

Upvotes: 0

Views: 1867

Answers (1)

Victor Levin
Victor Levin

Reputation: 1177

This means that some values in C1 column are not numeric strings. You cannot cast a string like '1 0' (with space in between) into number for example.

Try using SELECT C1 WHERE ISNUMERIC(C1) = 0 to determine where the problematic value is.

For Oracle: http://odiexperts.com/is_number-at-oracle-a-work-around/

Upvotes: 1

Related Questions