Обсуждение: 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_cltand 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_cltand 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
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
>
> ************
> 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) #
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
> > 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