Friday, August 9, 2019

Aliasing subqueries

Aliasing subqueries... is it really necessary?

I know, this one is questionable and probably most formalists will tell me it's Oracle that is wrong here. Maybe. Most likely. But my point here is usability and not really conformity to the rules. In this particular case I believe rules could be changed.
What am I talking about?

I'm talking about the necessity of aliasing subqueries. After years and years of using SAS and Oracle I found it very, very irritating to be forced to name all the queries you developing. It's really a good thing if you develop a habit of naming virtually every subquery/table right away. In typical scenario queries are being built step by step, often bottom up. We write some basic query and add things to it. Sometimes (meaning all the time) we combine data from multiple sources and sooner or later we find ourselves subquerying something. And only then (we, former Oracle devs) we find ourselves seeing stupid errors which force us to name the subquery.
select x from (
    select 1 as x 
) 
Msg 102, Level 15, State 1, Line 131 Incorrect syntax near ')'. 

Why Microsoft/ANSI, why?

To add a pinch of salt to the Oracle - after switching from SAS I found the need of removing as keyword from table/subquery aliases equally irritating... Why Oracle, why?

Couldn't find the exact document (ANSI standard) that would have told me what is the real standard but people seem to be referencing this:
FROM (<subquery>) [AS] <name>
This suggests only AS being an optional part and both subquery and name being required here. This means SQL Server is fully conforming to the standard (enforcing name and not requiring as). It's totally inverted for Oracle which is not requiring name at all and disallowing the use of the as keyword. If we try to do so we're gonna get:
select x from (
    select 1 as x from dual
) as dataset
SQL Error: ORA-00933: SQL command not properly ended

What are your thoughts on that?

Best

No comments:

Post a Comment

Non fractional division (int/float)

For me a RDBMS is a high level tool. Something very opposite to using low level languages like C, C++ or... Assembler. From user/developer p...