Skip site navigation (1) Skip section navigation (2)

Re: [SQL] select ... from (select .. from where.. ) where ... ?

From: Clark Evans <clark(dot)evans(at)manhattanproject(dot)com>
To: MESZAROS Attila <tilla(at)chiara(dot)csoma(dot)elte(dot)hu>
Cc: pgsql-sql(at)hub(dot)org
Subject: Re: [SQL] select ... from (select .. from where.. ) where ... ?
Date: 1999-03-31 20:00:45
Message-ID: 37027EED.1FC9EA51@manhattanproject.com (view raw or flat)
Thread:
Lists: pgsql-sql
MESZAROS Attila wrote:
> I'am faced with a huge system using the above kind of queries.
> It was originally written and tested with adabas.

I wrote a hudge system using those kind of queries.  Very
useful buggers, really helps you manage complexity.  I bet
it plays hudge games with the optimizer though...
 
> Unfortunatelly 6.4 does support subqueries only in the 'where' expression,
> as far as I undersood...

That's my understanding.  6.5 will not support it either.

> My questions are:
>         How can I port this program with the least effort?
>         Shall I create views corresponding to the subqueries,
>         and doing the outer select on a view?

You can do this, as long as the inner select does not
have an aggregate.  If it has an aggregate _or_ uses an
outer join then you need to code up functions that
do the outer-join.

>         Is is planned to implement this feature in the near future?

I hope so.  Actually, I think this is a generalization of the HAVING 
clause.  About 2 years ago (using Oracle) I dropped using the HAVING
clause due to this form... so, there is another possibility,
see if you can re-write these queries to use HAVING.

Let me know if I can help more.

Clark

In response to

pgsql-sql by date

Next:From: Frans de WetDate: 1999-03-31 20:53:49
Subject: Retrieving the record not matched with a join.
Previous:From: MESZAROS AttilaDate: 1999-03-31 19:35:18
Subject: select ... from (select .. from where.. ) where ... ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group