Received: from mx99.openu.ac.il (arbel.openu.ac.il [147.233.32.4])
	by hub.org (8.9.3/8.9.3) with ESMTP id LAA76350
	for <pgsql-sql@postgreSQL.org>; Wed, 2 Jun 1999 11:07:35 -0400 (EDT)
	(envelope-from herouth@oumail.openu.ac.il)
Received: from talmon.openu.ac.il (talmon.openu.ac.il [147.233.32.11])
	by mx99.openu.ac.il (Postfix) with ESMTP for <pgsql-sql@postgreSQL.org>
	id BEC0D4B534; Wed,  2 Jun 1999 18:07:34 +0300 (IDT)
Received: from [147.233.159.109] (mac-herouth.pc.openu.ac.il)
	by talmon.openu.ac.il (Netscape Messaging Server 3.54)
	with ESMTP id AAA1BE2; Wed, 2 Jun 1999 18:10:04 +0300
X-Sender: herouth@shaked.cc.openu.ac.il
Message-Id: <l03130305b37af76d6120@[147.233.159.109]>
In-Reply-To: <m10pCKk-0003kGC@orion.SAPserv.Hamburg.dsh.de>
References: <l03130304b37add804973@[147.233.159.109]> from "Herouth Maoz"
	at Jun 2, 99 05:06:53 pm
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Date: Wed, 2 Jun 1999 18:08:17 +0300
To: jwieck@debis.com (Jan Wieck)
From: Herouth Maoz <herouth@oumail.openu.ac.il>
Subject: Re: [SQL] Order by
Cc: jwieck@debis.com, pgsql-sql@postgreSQL.org

At 17:49 +0300 on 02/06/1999, Jan Wieck wrote:


>     GROUP BY must be  possible  in  a  view's  query  expression.
>     Without  that  views  with aggregate columns could never work
>     properly.
>
>     If I now read your above statements right, "query expression"
>     ->  "query specification" -> "table expression" also forbid's
>     GROUP BY, because it cannot occur in  a  table  expression  -
>     right?  That  means  that  a  fully SQL92 compliant DB cannot
>     support aggregates in view definitions?

No, "table expression" includes "group by" and "having", because they
define the data itself. They actually say something about the set of
tuples. Here is the "table expression" definition:


   <table expression> ::=
        <from clause>
        [ <where clause> ]
        [ <group by clause> ]
        [ <having clause> ]

Here is the "query specification":

   <query specification> ::=
        SELECT [ <set quantifier> ] <select list> <table expression>

   <select list> ::=
          <asterisk>
        | <select sublist> [ { <comma> <select sublist> }... ]

   <select sublist> ::=
          <derived column>
        | <qualifier> <period> <asterisk>

   <derived column> ::= <value expression> [ <as clause> ]

   <as clause> ::= [ AS ] <column name>

Finally, if you wondered where the syntax allows for unions and such like
for the definition of a view, here it is:

   <query expression> ::=
        <non-join query expression>
      | <joined table>

    <non-join query expression> ::=
        <non-join query term>
      | <query expression> UNION  [ ALL ] [ <corresponding spec> ] <query term>

      | <query expression> EXCEPT [ ALL ] [ <corresponding spec> ] <query term>


    <query term> ::=
        <non-join query term>
      | <joined table>

    <non-join query term> ::=
        <non-join query primary>
      | <query term> INTERSECT [ ALL ] [ <corresponding spec> ] <query primary>


    <query primary> ::=
        <non-join query primary>
      | <joined table>

    <non-join query primary> ::=
        <simple table>
      | <left paren> <non-join query expression> <right paren>

    <simple table> ::=
        <query specification>
      | <table value constructor>
      | <explicit table>

    <explicit table> ::= TABLE <table name>

    <corresponding spec> ::=
      CORRESPONDING [ BY <left paren> <corresponding column list> <right
paren> ]


    <corresponding column list> ::= <column name list>

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma