pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsubquery, except and view
5+ messages / 5 participants
[nested] [flat]
* subquery, except and view
@ 1999-12-07 14:43 Max Buvry <Max.Buvry@enseeiht.fr>
0 siblings, 2 replies; 5+ messages in thread
From: Max Buvry @ 1999-12-07 14:43 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] subquery, except and view
@ 1999-12-09 02:11 De Moudt Walter <wdemoudt@planetinternet.be>
parent: Max Buvry <Max.Buvry@enseeiht.fr>
1 sibling, 1 reply; 5+ messages in thread
From: De Moudt Walter @ 1999-12-09 02:11 UTC (permalink / raw)
To: Max Buvry <Max.Buvry@enseeiht.fr>; +Cc: pgsql-sql
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
>
> ************
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] subquery, except and view
@ 1999-12-09 08:08 marten@feki.toppoint.de
parent: Max Buvry <Max.Buvry@enseeiht.fr>
1 sibling, 0 replies; 5+ messages in thread
From: marten@feki.toppoint.de @ 1999-12-09 08:08 UTC (permalink / raw)
To: Max.Buvry@enseeiht.fr; +Cc: pgsql-sql
>
> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] subquery, except and view
@ 1999-12-09 15:27 Jan Wieck <wieck@debis.com>
parent: De Moudt Walter <wdemoudt@planetinternet.be>
0 siblings, 1 reply; 5+ messages in thread
From: wieck@debis.com @ 1999-12-09 15:27 UTC (permalink / raw)
To: wdemoudt@planetinternet.be; +Cc: Max.Buvry@enseeiht.fr; pgsql-sql
> 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 ...
Is definitely necessary, because 'howmuch' is an aggregate
column and the rewriter can only put that into the
qualification if the aggreate is on the right side of the
comparision.
At the time I added this capability at all, the operator
catalog was in a much too bad state WRT the
commutator/negator information. Otherwise I would have made
the rewriter automatically swap it for you.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck@debis.com (Jan Wieck) #
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] subquery, except and view
@ 1999-12-09 19:20 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jan Wieck <wieck@debis.com>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 1999-12-09 19:20 UTC (permalink / raw)
To: wieck@debis.com; +Cc: wdemoudt@planetinternet.be; Max.Buvry@enseeiht.fr; pgsql-sql
wieck@debis.com (Jan Wieck) writes:
>> 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 ...
> Is definitely necessary, because 'howmuch' is an aggregate
> column and the rewriter can only put that into the
> qualification if the aggreate is on the right side of the
> comparision.
BTW, this restriction is no longer true in current sources, since
single-result sub-SELECTs can now appear anywhere in an expression...
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~1999-12-09 19:20 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-12-07 14:43 subquery, except and view Max Buvry <Max.Buvry@enseeiht.fr>
1999-12-09 02:11 ` De Moudt Walter <wdemoudt@planetinternet.be>
1999-12-09 15:27 ` Jan Wieck <wieck@debis.com>
1999-12-09 19:20 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-12-09 08:08 ` marten@feki.toppoint.de
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