pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (5+ messages)  latest in thread

Message-ID: <199912071443.PAA11193@enseeiht.enseeiht.fr>
Permalink:  ../199912071443.PAA11193@enseeiht.enseeiht.fr/
Also on:    postgresql.org/message-id/199912071443.PAA11193@enseeiht.enseeiht.fr

 · 

reply

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