Another issue with to_number(). I simply do not understand it

Question

I have a master table (hereafter called SURVEY) and a detail table (hereafter called ANSWERS.) Unsurprisingly, ANSWERS has answers to SURVEY questions. ANSWERS has a VARCHAR2 column named TEXT. Some ANSWERS.TEXT values are truly text but some are actually numerics. Fortunately, I always know which rows contain text and which contain numbers-as-text.

This is the way it is. I can't change this.

Back in the day, when certain ANSWERS rows were saved, their TEXT values were cherry-picked and put into the SURVEY table in properly typed columns. A trivial one-table select would fetch SURVEYs and the special values.

But now, with the addition of a new application, we've removed the special columns. Instead, we now have to fetch the appropriate ANSWERS rows' TEXT values instead.

I have created a query that simulates the old trivial select statement. It works great... mostly.

Here's a snippet:

select survey.*, 
       j2.overall_score
  from survey,
       (select to_number(trim(ANSWER.text)) overall_score, 
               survey.id survey_id 
          from ANSWER, 
               [edited - more SQL that gets the 'score' row from ANSWERS]) j2      
 where
   survey.id=j2.survey_id
   and overall_score > 70    

You might note the j2. In the real query, there are six such columns, j1 through j6. When I run the query, it looks just like the old query. You can't tell it's really being assembled from a master/detail. That's a relief!

My problem, however, is that the 'overall_score > 70' phrase causes a '1722 invalid number' error. Oracle is as happy as a clam when I don't include the phrase, so all the output is passing through j2's to_number() function and looks good. But if I add the conditional, I fail.

The 'overall_score' part of the where clause is being added dynamically based upon search criteria entered from a web page.

I need some fu that tells Oracle I really do know what I'm doing, please do it. If there is non-numeric data, ok, let j2's to_number() fail. Cool. But otherwise, just do it.

Any wise words? I'm a contractor and time is nearly up. This is a new requirement :-/

Answer 1

I think that the optimizer is probably merging the inline view with the rest of the query, which means that the condition overall_score > 70 may be evaluated for rows that don't match the rest of the view's predicates, thereby hitting rows that don't contain numeric values in text.

If that's what happening, you should be able to prevent it by adding a hint in the first line of the query:

select /*+ NO_MERGE(j2) */ ...

Alternatively, it could be pushing the predicate into the view, in which case you would need the NO_PUSH_PRED hint. If you can generate an execution plan for the query, it will probably show what the exact issue is.

Answer 2

We created a special version of to_number which internally catches the '1722 invalid number' exception and returns 0 instead of. Replacing to_number with this new function in th sql eliminated this problem for us.