4

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 :-/

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Tony Ennis
  • 12,000
  • 7
  • 52
  • 73
  • 1
    Have you looked at what `j2` is returning, separately from the final query? I find it hard to believe that you're getting an invalid number error on the outer query--I'd expect the `TO_NUMBER` conversion to trigger the error. – OMG Ponies Sep 16 '10 at 15:37
  • If I remove the '> 70' conditional, the query returns rows. When I add the conditional, I get '1722 invalid number' sadness. – Tony Ennis Sep 16 '10 at 15:41
  • oops hit return instead of newline. When I run the j2 subquery alone, it returns valid rows without error. – Tony Ennis Sep 16 '10 at 15:41

2 Answers2

10

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.

Dave Costa
  • 47,262
  • 8
  • 56
  • 72
  • Will do. The DBA is creating me a plan table now :-D – Tony Ennis Sep 16 '10 at 16:26
  • Explain output is too long to post. I'm using oracle's UTLXPLS to see the result. What do you use? – Tony Ennis Sep 16 '10 at 16:55
  • I created a view of the unqualified query. Then I select from the view with the overall_score > 70. This also failed. That's curious unless Oracle's optimizer is smart enough to optimize views' internals when used in a select. I checked the data again, the column in question is always a numeric-as-text for the 'overall score' row. – Tony Ennis Sep 16 '10 at 17:06
  • Added NO_MERGE(j2) and NO_PUSH_PRED(j2) (and in fact for all j1..6) and got the same result. – Tony Ennis Sep 16 '10 at 17:55
  • Oracle will happily optimise a view's internals when used in a larger select. It just squishes the full SQL together as if the view wasn't there - the view just makes things easier for the person putting together the SQL. – Nick Pierpoint Sep 17 '10 at 12:16
  • I bet a materialzed view would have done the trick. I don't have the rights to create one to try it, however. – Tony Ennis Sep 17 '10 at 13:51
3

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.

asalamon74
  • 6,120
  • 9
  • 46
  • 60