From: De Moudt Walter <wdemoudt@planetinternet.be>
To: Max Buvry <Max.Buvry@enseeiht.fr>
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] subquery, except and view
Date: Thu, 09 Dec 1999 03:11:59 +0100
Message-ID: <384F0FEF.1BF044B1@planetinternet.be> (raw)
References: <199912071443.PAA11193@enseeiht.enseeiht.fr>
Hi Max,
This worked for me :
-first create a view that displays the distinct clients with their
amount of occurence in the command table, then use a querry on both the
view and the clients table :
first the view :
test :>create view commamount as
test :>select num_clt, count(*) as howmuch
test :>from command
test :>group by num_clt;
CREATE
test :>select client.num_clt, client.name from client, commamount where
test :>(client.num_clt = commamount.num_clt) and
test :>(3 > howmuch);
This seems to do the job perfectly. Notice the value preceeding the
column name in the comparison. Seems nescesary ...
Didn't test the second example. This does the trick anyway :-)
Greetings,
Walter De Moudt
Max Buvry wrote:
>
>
> Hi,
>
> Please, I need help to correct some sql queries...
>
> Let me define my db :
>
> CLIENT (NUM_CLT, NAME, ADR)
> ITEM (NUM_IT, NAME, PRICE)
> COMMAND(NUM_CLT, NUM_IT, QT)
>
> I meet two problems : one concerns subqueries or
> operator instruction "except" and the other, the 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.
>
>
> -------
> QUERY 2
> -------
>
> select clt.num_clt
> from client clt
> except
> select cd.num_clt
> from command cd
> group by cd.num_clt
> having count(*) > 2;
>
> The PostgreSQL Error is : parser: parse error at or next except
>
> If I use "(" and ")" for the subquery, the PostgreSQL Error
> is : parser: parse error at or next select.
>
> I try to check "except" with simple query and I don't succeed it.
>
> -------
> QUERY 3
> -------
>
> I want to create a view and I want to name the column.
> I don't find the exact syntax for this.
>
> create view payment( NAME_C, TT ) as
> select client.name NAME_C, sum(qt*prix) TT
> from client, item, command
> where client.num_clt=command.num_clt and item.num_item=command.num_item
> group by client.name, client.num_clt
>
> An other question : why the num_clt is selected also ?
>
>
> If I don't name the column, the query is correct and Postgres
> chooses the names sum in place of TT.
> (select * from payment returns the correct result) :
>
> create view payment as
> select client.name, sum(qt*prix)
> from client, item, command
> where client.num_clt=command.num_clt and item.num_item=command.num_item
> group by client.name, client.num_clt
>
> But when I want to know the client which must pay more than 1000 :
>
> select * from payment
> where sum>1000
>
> The PostgreSQL Error is : rewrite: aggregate column
> view must be at rigth side in qual
>
> In advance, thank you for your help.
>
> mb
>
> ************
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: wdemoudt@planetinternet.be, Max.Buvry@enseeiht.fr
Subject: Re: [SQL] subquery, except and view
In-Reply-To: <384F0FEF.1BF044B1@planetinternet.be>
References: <199912071443.PAA11193@enseeiht.enseeiht.fr> <384F0FEF.1BF044B1@planetinternet.be>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox