pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
subquery, except and view
5+ messages / 5 participants
[nested] [flat]

* subquery, except and view
@ 1999-12-07 14:43 Max Buvry <Max.Buvry@enseeiht.fr>
  1999-12-09 02:11 ` Re: [SQL] subquery, except and view De Moudt Walter <wdemoudt@planetinternet.be>
  1999-12-09 08:08 ` Re: [SQL] subquery, except and view marten@feki.toppoint.de
  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-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   ` Re: [SQL] subquery, except and view Jan Wieck <wieck@debis.com>
  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-07 14:43 subquery, except and view Max Buvry <Max.Buvry@enseeiht.fr>
  1999-12-09 02:11 ` Re: [SQL] subquery, except and view De Moudt Walter <wdemoudt@planetinternet.be>
@ 1999-12-09 15:27   ` Jan Wieck <wieck@debis.com>
  1999-12-09 19:20     ` Re: [SQL] subquery, except and view Tom Lane <tgl@sss.pgh.pa.us>
  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-07 14:43 subquery, except and view Max Buvry <Max.Buvry@enseeiht.fr>
  1999-12-09 02:11 ` Re: [SQL] subquery, except and view De Moudt Walter <wdemoudt@planetinternet.be>
  1999-12-09 15:27   ` Re: [SQL] subquery, except and view Jan Wieck <wieck@debis.com>
@ 1999-12-09 19:20     ` Tom Lane <tgl@sss.pgh.pa.us>
  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

* Re: [SQL] subquery, except and view
  1999-12-07 14:43 subquery, except and view Max Buvry <Max.Buvry@enseeiht.fr>
@ 1999-12-09 08:08 ` marten@feki.toppoint.de
  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


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