Received: from trends.net (root@clio.trends.ca [209.47.148.2]) by hub.org (8.9.3/8.9.3) with ESMTP id WAA02983 for ; Thu, 9 Dec 1999 22:10:20 -0500 (EST) (envelope-from feki.toppoint.de!marten@toppoint.de) Received: from mail.toppoint.de (LaForge.toppoint.de [195.244.243.6]) by trends.net (8.8.8/8.8.8) with SMTP id VAA03971 for ; Thu, 9 Dec 1999 21:35:35 -0500 (EST) Received: by mail.toppoint.de (Smail3.1.29.1) id ; Fri, 10 Dec 99 03:33 MET Received: (from marten@localhost) by feki.toppoint.de (8.9.3/8.9.3) id JAA03583; Thu, 9 Dec 1999 09:08:24 +0100 From: marten@feki.toppoint.de Message-Id: <199912090808.JAA03583@feki.toppoint.de> Subject: Re: [SQL] subquery, except and view In-Reply-To: <199912071443.PAA11193@enseeiht.enseeiht.fr> from Max Buvry at "Dec 7, 1999 3:43:54 pm" To: Max.Buvry@enseeiht.fr (Max Buvry) Date: Thu, 9 Dec 1999 09:08:24 +0100 (CET) Cc: pgsql-sql@postgresql.org X-Mailer: ELM [version 2.4ME+ PL47 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit > > I don't understand why the two queries below > are not correct with Postgresql though it be correct with Oracle > (I want to know the clients which have not > made more than 2 commands) : > > ------- > QUERY 1 > ------- > > select clt.num_clt > from client clt > where clt.num_clt not in > (select cd.num_clt > from commande cd > group by cd.num_clt > having count(*) > 2); > > The PostgreSQL Error is : rewrite: aggregate column > view must be at rigth side in qual > > I test the subquery and it is ok. > More or less this question is asked again and again - I've asked this before - perhaps 3-4 weeks ago - it should be put into a faq. It's a restriction of PostgreSQL and it's SQL parser. The aggregate column is count(*) and it "could" work perhaps if you rewrite it as ".... 2 <= count(*)". It was said on this list, that the rewriter might interpret the statement not correctly and return wrong results. You should have a closer look at the archive with subjects like having, group ... (last 8 weeks). Marten