Hello. I've recently started a new job where the company uses
Informix. Apparently it's version 9.4 on most systems. (If that's not
specific enough, I apologize). In any case, I come from a Postgres
background, and am used to filtering result sets using "LIMIT" and
"OFFSET". I have been trying to manage the same thing with Informix,
for the purposes of displaying a data grid of database values.

So far, I've found the "FIRST" command to match up to Postgres'
"LIMIT" just fine. If I only want 10 records, then "FIRST 10" works.
And in searching documentation and these groups, I found syntax for a
"SKIP" expression, which is almost identical to Postgres' "OFFSET".
With SKIP, if I want records 21 to 30, I could write "SKIP 20 FIRST
10".

The problem is, it looks like "SKIP" only works in Informix 10+,
leaving me without any idea how to return an offset number of results.
I've heard that cursor's are the way to go, but so far I've found no
good documentation (for version 9.x) on how I would go about this.

Does anyone know a way to duplicate "SKIP" functionality in older
version of Informix?

Thanks,

-Dan

On May 10, 8:06 am, dan.goye...@gmail.com wrote:
> Hello. I've recently started a new job where the company uses
> Informix. Apparently it's version 9.4 on most systems. (If that's not
> specific enough, I apologize). In any case, I come from a Postgres
> background, and am used to filtering result sets using "LIMIT" and
> "OFFSET". I have been trying to manage the same thing with Informix,
> for the purposes of displaying a data grid of database values.
>
> So far, I've found the "FIRST" command to match up to Postgres'
> "LIMIT" just fine. If I only want 10 records, then "FIRST 10" works.
> And in searching documentation and these groups, I found syntax for a
> "SKIP" expression, which is almost identical to Postgres' "OFFSET".
> With SKIP, if I want records 21 to 30, I could write "SKIP 20 FIRST
> 10".
>
> The problem is, it looks like "SKIP" only works in Informix 10+,
> leaving me without any idea how to return an offset number of results.
> I've heard that cursor's are the way to go, but so far I've found no
> good documentation (for version 9.x) on how I would go about this.
>
> Does anyone know a way to duplicate "SKIP" functionality in older
> version of Informix?

DECLARE c SCROLL CURSOR FOR "SELECT * FROM WhatEver";
OPEN c;

counter = 0;
FETCH ABSOLUTE :skip c INTO :host_var_list;
WHILE STATUS = 0 AND counter < limit
...stash host vars into array...
counter = counter + 1;
FETCH NEXT c INTO :host_var_list
END WHILE

CLOSE c;
FREE c;

The key point is the use of a scroll cursor - see Informix Guide to
SQL: Syntax manual under DECLARE (and FETCH).