Recipe14.15.Transforming Data with an Inline View

Recipe 14.15. Transforming Data with an Inline View

Problem

You have a table in a column that sometimes contains numeric data and sometimes character data. Another column in the same table indicates which is the case. You wish to use a subquery to isolate only the numeric data:

 select *   from ( select flag, to_number(num) num        from subtest        where flag in ('A', 'C') )        where num > 0 

Unfortunately, this query against an inline view often (but perhaps not always!) results in the following error message;

 ERROR: ORA-01722: invalid number 

Solution

One solution is to force the inline view to completely execute prior to the outer SELECT statement. You can do that, in Oracle at least, by including the row number pseudo-column in your inner SELECT list:

 select *   from ( select rownum, flag, to_number(num) num        from subtest        where flag in ('A', 'C') )        where num > 0 

See "Discussion" for an explanation of why this solution works.

Discussion

The reason for the invalid number error in the problem query is that some optimizers will merge the inner and outer queries. While it looks like you are executing an inner query first to remove all non-numeric NUM values, you might really be executing:

 select flag, to_number(num) num from subtest where to_number(num) > 0 and flag in ('A', 'C'); 

And now you can probably clearly see the reason for the error: rows with non-numeric NUM values are not filtered out before the TO_NUMBER function is applied.

Should a database merge sub and main queries? The answer depends on whether you are thinking in terms of relational theory, in terms of the SQL standard, or in terms of how your particular database vendor chooses to implement his brand of SQL. You can learn more by visiting http://gennick.com/madness.html.

The solution solves the problem, in Oracle at least, because it adds ROWNUM to the inner query's SELECT list. ROWNUM is a function that returns a sequentially increasing number for each row returned by a query. Those last words are important. The sequentially increasing number, termed a row number, cannot be computed outside the context of returning a row from a query. Thus, Oracle is forced to materialize the result of the subquery, which means that Oracle is forced to execute the subquery first in order to return rows from that subquery in order to properly assign row numbers. Thus, querying for ROWNUM is one mechanism that you can use to force Oracle to fully execute a subquery prior to the main query (i.e., no merging of queries allowed). If you are not using Oracle, and you need to force the order of execution of a subquery, check to see whether your database supports something analogous to Oracle's ROWNUM function.