TO_NUMBER invalid number error

2
votes

I have searched for and seen some similar questions, but none have solved my problem. I am getting a invalid number error on my TO_NUMBER in my where clause. Some things I have read said that it can be caused by where you place the TO_NUMBER in your where clause, so I was hoping someone could help me solve this. Here is the where clause in my query:

WHERE year_sec = TEST.YEAR_SEC_BY_DATE ('F', 0)
   AND (year_sec = '2014F')
    AND ((no_name LIKE '54%' AND user3 IS NOT NULL)
     OR (SUBSTR (no_name, 1, 2) = '52' AND SUBSTR (no_name, 5, 1) = '8')
     OR (no_name LIKE '56%')) 
OR (TO_NUMBER(SUBSTR(year_sec,1,4), '9999') >= 2015)
     AND ((no_name LIKE '543%' AND user3 IS NOT NULL)
      OR (no_name LIKE '523%')
      OR (no_name LIKE '563%'));

year_sec is always 4 digits with one letter after, and never null (if it isn't then there would be much bigger problems). So can anyone see why this would be causing the ORA-01722 error?

EDIT: Removed quotes, error while copying over sql.

2
sqloracletype-conversion
@Gordon that was an accident while copying the sql over. With the quotes removed, I still get the same error. - Dan
Are you sure it refers to the same line? - Mihai
I just ran it again, and got [Error] Execution (17: 19): ORA-01722: invalid number The referenced spot is the start of the SUBSTR function on the line starting with OR (TO_NUMBER... - Dan
It does not matter where in what order you put predicates in the where clause, the DBMS is free to evaluate them in any order. Are you sure that SUBSTR(year_sec,1,4) is always a number? What is the result of select SUBSTR(year_sec,1,4) from ... where SUBSTR(year_sec,1,1) NOT BETWEEN '0' AND '9' OR SUBSTR(year_sec,2,1) NOT BETWEEN '0' AND '9' OR ... for all four positions - Lennart

2 Answers

3
votes

Assuming that the error is coming from the to_number in your query and not, for example, from an invalid conversion in the year_sec_by_date function or somewhere else in your code, I would generally wager that Oracle is too dumb to lie to you. Somewhere in your data, you have at least one row where year_sec isn't a 4 digit number followed by a letter.

One way of finding the problem row would be to

CREATE OR REPLACE FUNCTION my_to_number( p_str IN VARCHAR2 )
  RETURN NUMBER
IS
BEGIN
  RETURN to_number( p_str );
EXCEPTION
  WHEN others THEN
    RETURN NULL;
END;

and then

SELECT year_sec, <<other columns>>
  FROM <<your table>>
 WHERE my_to_number( substr( year_sec, 1, 4 )) IS NULL

Note that if you have some rows where the year_sec is invalid that are being filtered out by other predicates in your query, there is no guarantee that Oracle will apply predicates in any particular order. Oracle may apply the to_number before or after any other predicate in the query.

0
votes

The optimizer can rewrite your query and change where you expect the conversion to occur. Check for bad character data where you expect numeric data.

SELECT year_sec,
       UPPER(SUBSTR(year_sec,1,4)) AS UPPER_YEAR,
       LOWER(SUBSTR(year_sec,1,4)) AS LOWER_YEAR
  FROM my_table
 WHERE UPPER(SUBSTR(year_sec,1,4)) != LOWER(SUBSTR(year_sec,1,4))

From OraFAQ ORA-01722