Re: [SQL] subquery, except and view

>
> 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