Databases
StackOverflow2013
Postsdbo
POAnother issue with to_number(). I simply do not understand it
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POAnother issue with to_number(). I simply do not understand it
    primarykey
    data
    text
    <p>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.</p> <p><strong>This is the way it is. I can't change this.</strong></p> <p>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.</p> <p>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.</p> <p>I have created a query that simulates the old trivial select statement. It works great... mostly.</p> <p>Here's a snippet:</p> <pre><code>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 &gt; 70 </code></pre> <p>You might note the j2. In the real query, there are six such columns, j1 through j6. When I run the query, it looks <em>just</em> like the old query. You can't tell it's really being assembled from a master/detail. That's a relief!</p> <p>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.</p> <p>The 'overall_score' part of the where clause is being added dynamically based upon search criteria entered from a web page.</p> <p>I need some <em>fu</em> 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.</p> <p>Any wise words? I'm a contractor and time is nearly up. This is a new requirement :-/</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USOMG Ponies
    1. USTony Ennis
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POAnother issue with to_number(). I simply do not understand it
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POAnother issue with to_number(). I simply do not understand it
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POAnother issue with to_number(). I simply do not understand it
      1. This table or related slice is empty.
      1. VTUpMod
    1. COHave 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.
      singulars
      1. POAnother issue with to_number(). I simply do not understand it
      1. USOMG Ponies
    2. COIf I remove the '> 70' conditional, the query returns rows. When I add the conditional, I get '1722 invalid number' sadness.
      singulars
      1. POAnother issue with to_number(). I simply do not understand it
      1. USTony Ennis
    3. COoops hit return instead of newline. When I run the j2 subquery alone, it returns valid rows without error.
      singulars
      1. POAnother issue with to_number(). I simply do not understand it
      1. USTony Ennis
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload