Re: Is there such a thing as a sub-select in Informix--HELP!!!
En Jeff Timmerberg va escriure el dia 24 Aug 2000, a les 17:04:
> In oracle, this is perfectly acceptable syntax:
>
> SELECT * FROM (SELECT * FROM table);>
> as is this
>
> SELECT * FROM (SELECT * FROM (SELECT * FROM table));>
> etc.
>
> Does Informix allow one to do sub-selects?
> If it does not, is there any way around this other than using temp-tables?
>
> Thanks in advance for your help!!!
> For more detail, see below.
> e-mail replies welcomed.
>
> Jeff Timmerbeg
> jtimmerberg@erac.com
>
> Obviously, what I'm tring to do is a bit more complicated, but the concept
> is the same.
> Specifically, I am trying to retreive rows n through n+50 of a result set
> ordered by a particular column. I wish to use the following syntax assuming
> n = 200:
>
> SELECT FIRST 50 *
> FROM (
> SELECT FIRST 250 *
> FROM TABLE
> ORDER BY column DESC
> )
> ORDER BY column ASC;
Try with temporal tables;
Select first 250 *
from table
where conditions
order by desc column_a
into temp temptable with no log;
select first 50 *
from temptable
orderby column_a;
I hope this helps.
---------------------------------------
Isidre PONS ROCA
BASE - Gesti' d'Ingressos Locals
(Diputacio de Tarragona)
Servei de Sistemes d'Informacio
Av President Lluis Companys 12-C
43005 - Tarragona
SPAIN
Tel # +34 977 236731
Fax # +34 977 227302
http://www.altanet.org
ipons@dtgna.altanet.org
---------------------------------------
Isidre PONS ROCA wrote:
>
> En Jeff Timmerberg va escriure el dia 24 Aug 2000, a les 17:04:
>
> > In oracle, this is perfectly acceptable syntax:
> >
> > SELECT * FROM (SELECT * FROM table);> >
> > as is this
> >
> > SELECT * FROM (SELECT * FROM (SELECT * FROM table));> >
> > etc.
> >
> > Does Informix allow one to do sub-selects?
> > If it does not, is there any way around this other than using temp-tables?
> >
> > Thanks in advance for your help!!!
> > For more detail, see below.
> > e-mail replies welcomed.
> >
> > Jeff Timmerbeg
> > jtimmerberg@erac.com
> >
> > Obviously, what I'm tring to do is a bit more complicated, but the concept
> > is the same.
> > Specifically, I am trying to retreive rows n through n+50 of a result set
> > ordered by a particular column. I wish to use the following syntax assuming
> > n = 200:
> >
> > SELECT FIRST 50 *
> > FROM (
> > SELECT FIRST 250 *
> > FROM TABLE
> > ORDER BY column DESC
> > )
> > ORDER BY column ASC;>
> Try with temporal tables;
Or temporary tables...
>
> Select first 250 *
> from table
> where conditions
> order by desc column_a
> into temp temptable with no log;
You cannot have an ORDER BY clause in a query with an INTO TEMP clause.
Art S. Kagel
> select first 50 *
> from temptable
> orderby column_a;>
> I hope this helps.
>
> ---------------------------------------
> Isidre PONS ROCA
> BASE - Gestió d'Ingressos Locals
> (Diputacio de Tarragona)
> Servei de Sistemes d'Informacio
> Av President Lluis Companys 12-C
> 43005 - Tarragona
> SPAIN
> Tel # +34 977 236731
> Fax # +34 977 227302
> http://www.altanet.org
> ipons@dtgna.altanet.org
> ---------------------------------------