Specific help creating a scroll cursor

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.databases.informix/browse_thread/thread/df4f33140da0fd6f/6b6ea46021eeaf06?lnk=st&q=informix+scroll+cursor&rnum=19#6b6ea46021eeaf06 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.databases.informix/browse_thread/thread/df4f33140da0fd6f/6b6ea46021eeaf06?lnk=st&q=informix+scroll+cursor&rnum=19#6b6ea46021eeaf06 > > 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/