Re[2]: to_number question

From: Jonathan Gennick <jonathan_at_gennick.com>
Date: Thu, 15 Jul 2004 09:52:54 -0400
Message-ID: <70984954930.20040715095254@gennick.com>

Thursday, July 15, 2004, 8:45:18 AM, Wolfgang Breitling (breitliw_at_centrexcc.com) 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:

The subquery defines a *new* table of rows.
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@gennick.com

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_at_gennick.com 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_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Received on Thu Jul 15 2004 - 08:49:46 CDT