Received: from enseeiht.enseeiht.fr (enseeiht.enseeiht.fr [147.127.18.144]) by hub.org (8.9.3/8.9.3) with ESMTP id JAA40250 for ; Tue, 7 Dec 1999 09:43:45 -0500 (EST) (envelope-from Max.Buvry@enseeiht.fr) Received: from mata.enseeiht.fr by enseeiht.enseeiht.fr; Tue, 7 Dec 1999 15:43:54 +0100 (MET) Date: Tue, 7 Dec 1999 15:43:54 +0100 (MET) From: Max Buvry X-Url: http://www.enseeiht.fr Message-Id: <199912071443.PAA11193@enseeiht.enseeiht.fr> To: pgsql-sql@postgresql.org Subject: subquery, except and view 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