Re: Restricting queries to a certain number of rows?

Spetc@aol.com wrote: > > }FOREACH cursor INTO something.* > } LET counter = counter + 1 > } IF counter >= 100 THEN > } EXIT FOREACH > } END IF > }END FOREACH > > Wouldn't this still take "ages", because of the fact that FOREACH would still > open a cursor and select all the data (say 10,000 rows) ? ( I believe we're talking 4GL at this point... and possibly an interactive application ) Not if you optimize your selected set. By this I mean using a selected-set of KEYS, that you can in turn use to select the bulk of data from individual rows. Pre 7.x I would recommend using ROWIDs, but we just went through this discussion, so rewind the month, and read the posts. Use a serial or primary key of some sort. Advantages to using a selected-set of KEYs for an application where you want to browse rows returned from a query, and possibly add, update rows: 1. If you do a Next/Previous, the KEY can point to the ROW, a second cursor grabs the row, and displays the most current information from the data base. You can see this kind of behaviour if you update a row in an ISQL screen. 2. You can ORDER BY the KEYs if you like. The selected-set of KEYS will be smaller than grabbing the whole row, so ORDER BY in your SELECT statement can be a viable option. It will slow your app down a bit, but can be speeded up with proper indexes, provided you have good data base design. The selected-set of KEYs will then be available for Next, Previous, Update functions. 3. If you use ORDER BY, you can't do an UPDATE WHERE CURRENT OF cursor on an active selected-set. If your selected-set is a list of KEYs, then you simply open a row FOR UPDATE if the user wants to UPDATE a row, by creating an UPDATE CURSOR on the row, where ROW is based on the KEY. This locks the row for update. For example: a. User enters UPDATE mode in the application. They now have the row locked. (This can also be arranged so the row is locked only at the moment of update if you have users who camp out in update mode and don't actually update a row quickly. Using a timeout mechanism is another possibility. ) b. They make their changes. c. They press ESC to engage the UPDATE. d. The row is updated. e. The cursor of KEYs should still be active if WITH HOLD has been used when you declare your cursor. f. After UPDATE, the user presses Next/Previous, and current information, including the data they just updated, shows up. Had you used just the active set for update, you wouldn't see the changes till you did a query again, or use WHERE CURRENT OF CURSOR without using ORDER BY in your SELECT statement. This is but one of a few possible solutions, so experiment! And if you get a chance check out my code generator called bas_4gl1. It's at my homepage. Inside bas_4gl1 is the code for the scenario just discussed. Cheers, Tim -- \\\\|// (6 6) ==============================---o00--(_)--00o---============================ Tim Schaefer tschaefe@encore.com tschaefe@shadow.net www.shadow.net/~tschaefe =============================================================================