From: Max Buvry <Max.Buvry@enseeiht.fr>
To: pgsql-sql@postgresql.org
Subject: subquery, except and view
Date: Tue, 7 Dec 1999 15:43:54 +0100 (MET)
Message-ID: <199912071443.PAA11193@enseeiht.enseeiht.fr> (raw)
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: Max.Buvry@enseeiht.fr
Subject: Re: subquery, except and view
In-Reply-To: <199912071443.PAA11193@enseeiht.enseeiht.fr>
References: <199912071443.PAA11193@enseeiht.enseeiht.fr>
* 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