Re: [SQL] subquery, except and view

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