It looks like it will work, but like you said its not pretty.

You could avoid the temp table and use a stored procedure.

Simply skip through the cursor to the required start point and return the
records from there. Of course if you need to update the records you had best
have a unique key...

Mark

----- Original Message -----
From: "John Carlson" <John_Carlson@whsmithusa.com>
To: "'Mark Denham'" <mkdenham@comcast.net>; <ids@iiug.org>
Sent: Friday, August 22, 2003 12:50
Subject: RE: Limiting Query [1745]


> True . . . but there is a way . . . . I think . . . but it isn't smooth
>
> Issue: I want records 500 through 520 of a given query
>
> -----------------------------------------------------------
>
>
> -- get the initial records, dump to temp file since 'first <N>' doesn't
> allow temp table usage
> unload to "temp.file"
> select first 520 *
> from some_table
> order by col1, col2, col3, col4
> ;
>
> --- create temp table to store data
> select *
> from some_table
> where 1=0
> into temp jctmp1
> ;
>
> -- load data
> load from 'temp.file'
> insert into jctmp1
> ;
>
> -- get records
> select first 21 *
> from jctmp1
> order by col1 desc, col2 desc, col3 desc, col4 desc
> ;
>
> ------------------------------------------------------------
>
> Would this do?
>
>
> -----Original Message-----
> From: Mark Denham [mailto:mkdenham@comcast.net]
> Sent: Friday, August 22, 2003 11:09 AM
> To: ids@iiug.org
> Subject: Re: Limiting Query [1745]
>
>
> If you are using 4gl, you can jump to the start of the record set that you
> want.
>
> Informix does not support this in sql. You can only limit the dataset to
> records at the start or end of the set.
>
> Mark
>
> ----- Original Message -----
> From: "James Kaufman " <jmk@kaufman.eden-prairie.mn.us>
> To: <ids@iiug.org>
> Sent: Friday, August 22, 2003 08:18
> Subject: Limiting Query [1742]
>
>
> > In other dbms, there are ways to retrieve data for a report and limit
> > the amount of data that is returned. For example, it is possible to
> > create a
> query
> > that defines a lot of criteria, but only returns records 500-520 (eg,
> using a
> > 'limit' clause).
> >
> > I have just installed Informix 9.40.UC2 onto a Red Hat Linux box. I
> > don't
> see a
> > way to limit the report data. Suggestions?
> >
> > --
> > Jim Kaufman mailto:jmk@linuxforbusiness.net
> > Linux Evangelist cell: 612-481-9778
> > public key 0x6D802619 fax: 952-937-9832
> > http://www.linuxforbusiness.net
> >
>
>
> "CONFIDENTIALITY NOTICE: This message originates from WHSmith USA Travel
> Retail. This email message and all attachments may contain legally
> privileged and confidential information intended solely for the use of the
> addressee. If you are not the intended recipient, you should immediately
> stop reading this message and delete it from the system. Any unauthorized
> reading, distribution, copying, or other use of this message or its
> attachments is strictly prohibited. All personal messages express solely
the
> sender's views and not those of WHSmith USA Travel Retail. This message
may
> not be copied or distributed without this disclaimer."

sending to informix-list