Tim,

Is the syntax for defining a table from a subquery (AS <QueryExpression>
[WITH DATA|WITHOUT DATA]) supposed to be compliant with SQL:2003 syntax ?

Ole Willy Tuv

Ole,

<< Is the syntax for defining a table from a subquery (AS <QueryExpression>
[WITH DATA|WITHOUT DATA]) supposed to be compliant with SQL:2003 syntax ?
>>

Yes.  Where is it not ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<< Yes.  Where is it not ? >>

WITHOUT DATA. The standard syntax is WITH NO DATA.

Also, the query expression should be a subquery, meaning that parentheses
around the query expression are required.

A compatible BNF would be:

AS (<QueryExpression>)  [WITH DATA|WITH NO DATA]

Example:

create table table2 as
(
 select col1, col2, col3
 from table1
)
with no data

Ole Willy Tuv

Ole,

<< WITHOUT DATA. The standard syntax is WITH NO DATA. >>

Damn, that was one of those reading what you want to see issues.

<< Also, the query expression should be a subquery, meaning that parentheses
around the query expression are required. >>

I might relax that requirement, similar to the fact that we allow an ORDER
BY also, which normally isn't allowed with a simple query expression.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<< Damn, that was one of those reading what you want to see issues. >>

Yes, but one that's easy to understand, since the starting standard BNF for
the clause is <with or without data>.

Btw, the full standard BNF is:

<as subquery clause> ::=
   [ <left paren> <column name list> <right paren> ] AS <subquery>
   <with or without data>

<with or without data> ::=
   WITH NO DATA
 | WITH DATA

<subquery> ::= <left paren> <query expression> <right paren>

<< I might relax that requirement, similar to the fact that we allow an
ORDER BY also, which normally isn't allowed with a simple query expression.
>>

Sure, I don't see any harm in that, except that it might be an advantage to
have an explicit syntax for subqueries, to avoid potential parser confusions
between query expressions and subquery expressions.

Ole Willy Tuv

Ole,

<< Yes, but one that's easy to understand, since the starting standard BNF
for the clause is <with or without data>. >>

Yes, now I know where I got it from.

<< Sure, I don't see any harm in that, except that it might be an advantage
to have an explicit syntax for subqueries, to avoid potential parser
confusions between query expressions and subquery expressions. >>

Right now we simply require that if you want a clause to be included in the
sub-query, then you'll need to enclose it in parentheses to ensure that EDB
doesn't confuse it with a clause for the outer query instead.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<< Right now we simply require that if you want a clause to be included in
the sub-query, then you'll need to enclose it in parentheses to ensure that
EDB doesn't confuse it with a clause for the outer query instead. >>

OK, sounds good to me.

Ole Willy Tuv