About a week ago I was here asking how to implement SKIP functionality
in Informix 9.x (since it's not until version 10.x+ that the SKIP
keyword was introduced). My question was answered in this post:

http://groups.google.com/group/comp....6ea46021eeaf06

Since then, I've been trying to get a cursor working, without success.
It seems all the documentation I can find on it is either vague, or
assumes I know some things that it does not explain. I've swapped
things around in that cursor declaration suggested in the above post,
but everything gives a very non-informative "A syntax error has
occured" message.

So, I'm asking for a bit of help again. Specifically, as a concrete
example, if someone can show me the exact Informix code they would use
to perform the following query on the sample table and data I'll
describe here. To see a complete example that really works seems to be
what I need at this point.

Consider this table of data:

CREATE TABLE test_table
id SERIAL NOT NULL PRIMARY KEY,
name CHAR(40),
created DATE default TODAY
);

Now, consider this sample data inserted into it:

INSERT INTO test_table (name) VALUES ('Ed');
INSERT INTO test_table (name) VALUES ('Diane');
INSERT INTO test_table (name) VALUES ('Bill');
INSERT INTO test_table (name) VALUES ('Tim');
INSERT INTO test_table (name) VALUES ('Sarah');
INSERT INTO test_table (name) VALUES ('Mike');

It's just some arbitrary data. Now, I want to be able to select X
number of records, offset by Y number of records. In other words,
select 2 records after skipping 3 records. In that case, I'd end up
with Tim and Sarah.

Can someone give me the actual cursor declaration to make this happen?
I'm sorry to be so needy here, but I've hit a brick wall on this
issue, and I don't seem to be getting anywhere.

If you choose to respond, please make sure the cursor is fully
functional for this table, and that if I were to execute the query, it
would return the results I'm after. I was all of the data on those two
rows, not just the name.

Thanks,

-Dan

dan.goyette@gmail.com wrote:
> About a week ago I was here asking how to implement SKIP functionality
> in Informix 9.x (since it's not until version 10.x+ that the SKIP
> keyword was introduced). My question was answered in this post:
>
> http://groups.google.com/group/comp....6ea46021eeaf06
>
> Since then, I've been trying to get a cursor working, without success.
> It seems all the documentation I can find on it is either vague, or
> assumes I know some things that it does not explain. I've swapped
> things around in that cursor declaration suggested in the above post,
> but everything gives a very non-informative "A syntax error has
> occured" message.
>
> So, I'm asking for a bit of help again. Specifically, as a concrete
> example, if someone can show me the exact Informix code they would use
> to perform the following query on the sample table and data I'll
> describe here. To see a complete example that really works seems to be
> what I need at this point.
>
> Consider this table of data:
>
> CREATE TABLE test_table
> id SERIAL NOT NULL PRIMARY KEY,
> name CHAR(40),
> created DATE default TODAY
> );
>
> Now, consider this sample data inserted into it:
>
> INSERT INTO test_table (name) VALUES ('Ed');
> INSERT INTO test_table (name) VALUES ('Diane');
> INSERT INTO test_table (name) VALUES ('Bill');
> INSERT INTO test_table (name) VALUES ('Tim');
> INSERT INTO test_table (name) VALUES ('Sarah');
> INSERT INTO test_table (name) VALUES ('Mike');
>
> It's just some arbitrary data. Now, I want to be able to select X
> number of records, offset by Y number of records. In other words,
> select 2 records after skipping 3 records. In that case, I'd end up
> with Tim and Sarah.
>
> Can someone give me the actual cursor declaration to make this happen?
> I'm sorry to be so needy here, but I've hit a brick wall on this
> issue, and I don't seem to be getting anywhere.
>
> If you choose to respond, please make sure the cursor is fully
> functional for this table, and that if I were to execute the query, it
> would return the results I'm after. I was all of the data on those two
> rows, not just the name.

Which language? I'm going to use I4GL.

DATABASE yours

MAIN

DEFINE r RECORD LIKE test_table.*

WHENEVER ERROR STOP
DECLARE c SCROLL CURSOR FOR
SELECT id, name ego, created superego
FROM test_table
ORDER BY id
OPEN c
FETCH ABSOLUTE 4 c INTO r.*
DISPLAY "Tim: ", r.*
FETCH NEXT c INTO r.*
DISPLAY "Sarah: ", r.*
CLOSE c

END MAIN

You can replace 4 with an integer variable for the position you want to
get to. You can replace the following fetch in a loop to collect more
than one subsequent record. You can use arrays if you like. The
looping structure was illustrated in the previous post to which you
referred. Yes, you need to clean up the syntax for the language you are
using, but since you don't tell use what you are using, we can't easily
help you on that score.

See: http://www.catb.org/~esr/faqs/smart-questions.html

[OK: I've not actually run that past a compiler. There might be a
syntax error in there - or worse.]

--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2007.0226 -- http://dbi.perl.org/