Comment on ADQL PR

[Patrick Dowler]

Another data point:

As far as I can tell, DB2 supports only "create table as ( select ... )" and not
"select into table ...". "select into" is only used to put values into variables.
We are running DB2 EEE 8.1. 

Patrick

On 8.6.2005 05:26, Francois Ochsenbein wrote:
> 
> You are right Clive, the SQL92 standard effectively indicates that
> the "into" clause represents a set of variables. The SQL standardisation
> came only late, when it turned out that each data-base vendor implemented
> his own SQL dialect... Postgres accepts SELECT INTO TABLE table_name,
> MS-SQL and Sybase don't need the "TABLE" keyword, Oracle accepts
> it as a "record" cursor... 
> 
> Is the concept of variables and cursors of any use to ADQL ? 
> 
> --Francois
> 
> >
> >On Mon, 6 Jun 2005, Francois Ochsenbein wrote:
> >
> >> Well, I'm surprised -- and doubt that Postgres really represents THE SQL
> >> standard ... SELECT INTO exists since the first versions of SQL,
> >> and does generates a table (or a relation if you prefer the "purist"
> >> relational language)
> >
> >I hate to disagree with someone with as much experience as Francois, but
> >my reading of the SQL1992 Standard (the latest one I can find online) is
> >that SELECT INTO only refers to a [list of] variables, not a table.  The
> >syntax is pretty clear, but the semantics in the Standard are pretty
> >hard to understand, so my interpretation could be wrong.
> >
> >The PostgreSQL reference manual is quite helpful about describing any
> >differences between its syntax and that of Standard SQL.  It has this to
> >say about SELECT INTO:
> >
> ><quote>
> >SQL92 uses SELECT ... INTO to represent selecting values into scalar
> >variables of a host program, rather than creating a new table.  [snip] The
> >PostgreSQL usage of SELECT INTO to represent table creation is historical.
> >It is best to use CREATE TABLE AS for this purpose.
> ></quote>
> >
> >There is an excellent compilation of differences in the implementation of
> >SQL among popular DBMS at http://troels.arvin.dk/db/rdbms/ although
> >unfortunately it does not deal with this particular issue.  But the only
> >conclusion that can be drawn from reading this compilation is that the
> >whole subject is a can of worms: *none* of the main DMBS vendors takes
> >*any* serious notice of *any* SQL Standards, so I don't see why our ADQL
> >should be constrained by them.
> >
> >My suggestion is that we should continue to use SELECT INTO <newtable> in
> >the way that we originally planned, rather than have to get involvind in
> >adding a whole new syntax tree for CREATE TABLE AS, when we don't at
> >present support anything like CREATE TABLE.
> >
> 
> ================================================================================
> Francois Ochsenbein       ------       Observatoire Astronomique de Strasbourg
>    11, rue de l'Universite F-67000 STRASBOURG       Phone: +33-(0)390 24 24 29
> Email: francois at astro.u-strasbg.fr   (France)         Fax: +33-(0)390 24 24 32
> ================================================================================
> 

-- 
Patrick Dowler
Tel/Tél: (250) 363-6914                  | fax/télécopieur: (250) 363-0045
Canadian Astronomy Data Centre   | Centre canadien de donnees astronomiques
National Research Council Canada | Conseil national de recherches Canada
Government of Canada                  | Gouvernement du Canada
5071 West Saanich Road               | 5071, chemin West Saanich
Victoria, BC                                  | Victoria (C.-B.)