Re: In-line views

From: Russ <rgoring_at_sbcglobal.net>
Date: 10 Dec 2001 09:49:52 -0800
Message-ID: <9d329c10.0112100949.6bb5b35c_at_posting.google.com>

71062.1056_at_compuserve.com (--CELKO--) wrote in message news:<c0d87ec0.0112080951.6abe0364_at_posting.google.com>...
> >> I've recently noticed that the in-line view SQL syntax used by
> Oracle does not work in Sybase. Are in-line views an SQL extension
> only supported by Oracle, or have other DBMSs failed to implement the
> SQL standard? <<
>
> When you say "in-line views" do you mean derived tables? The syntax
> is
>
> <tabular query expression> AS <table name> [<column name list>]
>
> SQL server and DB2 have this feature now. There are some scoping
> rules that apply to a derived table. Basically, once it has a name of
> its own, you cannot reference the names of tables inside it in the
> containing queries any more. While a derived table is supposed to act
> as if it was materialized, the optimizer can expand it in line if that
> will not change the results.

Just to sure the termilogy is correct, let me clarify my definition of "in-line view". Take the following example:

SELECT emp_name
FROM EMP,

     (SELECT count(emp_no) AS emp_count, dept
      FROM EMP
      GROUP BY dept having count(emp_no) < 20) AS DEPT_COUNT

WHERE EMP.dept = DEPT_COUNT.dept

The following is essentially a view on table EMP.

     (SELECT count(emp_no) AS emp_count, dept
      FROM EMP
      GROUP BY dept having count(emp_no) < 20) 

However, it is defined in-line with the SQL syntax - hence the term "in-line view".

I'm not familiar with the term "derived table", but then again I've worked primarily with Oracle. I'm not sure if we're talking about the same thing. Received on Mon Dec 10 2001 - 18:49:52 CET