Another problem with to_number (). I just don't get it

Question

I have a main table (hereinafter referred to as SURVEY) and a detailed table (hereinafter referred to as ANSWERS.) It is not surprising that ANSWERS answers to SURVEY questions. ANSWERS has a VARCHAR2 column named TEXT. Some ANSWERS.TEXT values ​​are really text, but some of them are actually numbers. Fortunately, I always know which lines contain text and which contain numbers as text.

The way it is. I can’t change that.

On the same day that some ANSWERS rows were saved, their TEXT values ​​were selected by cherry and placed in the SURVEY table in correctly printed columns. A trivial selection from one table will extract SURVEY and special values.

But now, adding a new application, we have removed the special columns. Instead, we now need to get the corresponding TEXT values ​​for the ANSWERS strings.

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

Here is 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 can mark j2. In a real query, there are six such columns, from j1 to j6. When I run the query, it looks the same as the old query. You cannot say that this is really going from the master / part. This is a relief!

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

The "total_score" part of the where clause is added dynamically based on the search criteria entered from the web page.

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

Any wise words? I am a contractor, and time is almost up. This is a new requirement: - /

Answer 1

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

Answer 2

I think that the optimizer probably combines the inline view with the rest of the query, which means that the condition overall_score > 70 can be evaluated for rows that don't match the rest of the presentation predicates, thereby striking rows that don't contain numeric values ​​in text .

If this happens, you should be able to prevent this by adding a hint to the first line of the query:

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

Alternatively, this can lead to a predicate in the view, in which case you will need the NO_PUSH_PRED hint. If you can create a query execution plan, it will probably show which problem.