Re[2]: to_number question

Jonathan Gennick <jonathan@xxxxxxxxxxx> · Thu, 15 Jul 2004 09:52:54 -0400

Thursday, July 15, 2004, 8:45:18 AM, Wolfgang Breitling 
(breitliw@xxxxxxxxxxxxx) wrote:
WB> These are the "rules". If you don't like them, go back to hierarchical 
WB> databases.

No, please, not that. Anything but that :-)

WB> Using subqueries does not impose any order on the processing.

I can agree up to a point. When it comes to physical
processing, I do not care what the database does. However,
from a logical perspective, when I select from a table, I
expect my select statement to consider only those rows in
that table. For example:

   SELECT a, to_number(b)
   FROM subtest;

This is a simple case. Clearly the database should look only
at the subtest table, and not apply to_number to data from
some other table.

But perhaps subtest is not the table I wish to work with.
Instead, I wish to create a table that is a subset of
the rows in subtest. I can do that by writing a subquery:

   SELECT a, to_number(b)
   FROM ( SELECT a, b
          FROM subtest
          WHERE a = 'a' );

I admit that I have not gone back to the ANSI standard to
read in detail what it says about this particular case.
However, I have a very difficult time understanding how you
can look at the above query and come up with any other
explanation of it than the following:

1. The subquery defines a *new* table of rows.

2. The main query operates against the new table returned by
the subquery.

Is there really something in the ANSI standard that says
otherwise? Does relational theory really allow the outer
query to execute against rows that are not in the "table"
I've listed in my FROM clause?

Best regards,

Jonathan Gennick --- Brighten the corner where you are
http://Gennick.com * 906.387.1698 * mailto:jonathan@xxxxxxxxxxx

Join the Oracle-article list and receive one
article on Oracle technologies per month by 
email. To join, visit http://five.pairlist.net/mailman/listinfo/oracle-article, 
or send email to Oracle-article-request@xxxxxxxxxxx and 
include the word "subscribe" in either the subject or body.

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: