Postgres Pro
Downloads
Home   >   mailing lists

Thread: Problems with complex queries ...

Problems with complex queries ...

From
"J. Fernando Moyano"
Date:
Hey everybody !!!
I am new on this list !!!

I have a little problem .....

I try this on my system: (Postgres 6.5.2, Linux)

"select n_lote from pedidos except select rp.n_lote from relpedidos rp,
relfacturas rf where  rp.n_lote=rf.n_lote group by rp.n_lote having
sum(rp.cantidad)=sum(rf.cantidad)"

and I get this result:

ERROR: rewrite: comparision of 2 aggregate
columns not supported 

but if I try this one:

"select rp.n_lote from relpedidos rp, relfacturas rf where 
rp.n_lote=rf.n_lote group by rp.n_lote having sum(rp.cantidad)=sum(rf.cantidad)"

It's OK !!

What's up???
Do you think i found a bug  ???
Do exists some limitation like this in subqueries??

(Perhaps Postgres don't accept using aggregates in subqueries ???)

I tried this too:

"select n_lote from pedidos where n_lote not in (select rp.n_lote from
relpedidos rp, relfacturas rf where  rp.n_lote=rf.n_lote group by rp.n_lote
having sum(rp.cantidad)=sum(rf.cantidad))"

but the result was the same !

And i get the same error message (or similar) when i try other variations.

Thanks !!!

Fer

--  ************* ******   ******  **********  *****   *****    ********************   ***** ******   **********
****** *****  ***********   *****         ********        ****     *************  ****    ****  *****           ****
     ****     ***** *******  ****    **** *****          *******        ****     *****  ******  ****    *********
****** *****   *********  *****   *****  ************
 
*****       ******   ****** *********  *****   *****    ******** 
(*) SymeX ==> http://www.lantik.com(*) Web en http://www.arrakis.es/~txino      (*) Informate sobre LINUX en
http://www.linux.org

Re: Problems with complex queries ...

From
Tom Lane
Date:
"J. Fernando Moyano" <txinete@wanadoo.es> writes:
> I try this on my system: (Postgres 6.5.2, Linux)

> "select n_lote from pedidos except select rp.n_lote from relpedidos rp,
> relfacturas rf where  rp.n_lote=rf.n_lote group by rp.n_lote having
> sum(rp.cantidad)=sum(rf.cantidad)"

> and I get this result:
> ERROR: rewrite: comparision of 2 aggregate columns not supported 

I think this is fixed in 7.0.
        regards, tom lane

Re: Problems with complex queries ...

From
Stephan Szabo
Date:
Without seeing the schema or anything, a similar
query to your first one appears to run on my 
Postgres 7.0.2 setup.  It's probably worth upgrading.

On Wed, 30 Aug 2000, J. Fernando Moyano wrote:
> I try this on my system: (Postgres 6.5.2, Linux)
> 
> "select n_lote from pedidos except select rp.n_lote from relpedidos rp,
> relfacturas rf where  rp.n_lote=rf.n_lote group by rp.n_lote having
> sum(rp.cantidad)=sum(rf.cantidad)"
> 
> and I get this result:
> 
> ERROR: rewrite: comparision of 2 aggregate
> columns not supported 
> 
> but if I try this one:
> 
> "select rp.n_lote from relpedidos rp, relfacturas rf where 
> rp.n_lote=rf.n_lote group by rp.n_lote having sum(rp.cantidad)=sum(rf.cantidad)"
> 
> It's OK !!
> 
> What's up???
> Do you think i found a bug  ???
> Do exists some limitation like this in subqueries??
> 
> (Perhaps Postgres don't accept using aggregates in subqueries ???)
> 
> I tried this too:
> 
> "select n_lote from pedidos where n_lote not in (select rp.n_lote from
> relpedidos rp, relfacturas rf where  rp.n_lote=rf.n_lote group by rp.n_lote
> having sum(rp.cantidad)=sum(rf.cantidad))"
> 
> but the result was the same !
> 
> And i get the same error message (or similar) when i try other variations.