Re: "SKIP" functionality in Informix 9.x ?

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).