Tweet
You might want to try this SQL command Select Field1, Field2, Fieldxyz, .... RRN(FileName) From FileName It will return the Relative Record number to you, maybe that'll help. Also, you could try Select Count(*) as NumRecs From FileName and then just use a counter, so that you can scroll back and forth with in your subfile. As you load your SubFile, add 15 (or 10) to your CountRecLocation. If Key = PageDown Then Add 15 to CountRecLocation Else If Key = PageUp Then Subtract 15 from CountRecLocation End-if End-If Hope this Helps.
-
-
V5R3 SQL Question
Hi, if I have to page through a result set, I sometimes use the statement specified below. In this statement only the rows 41 to 50 are returned. To page throug, you only have to change the number of rows specified in the Fetch First Clauses. Hope it helps
Code -
V5R3 SQL Question
B.Hauser - The description of your sql statement is exactly what I am trying to accomplish. However, when I run it, I always receive rows 1-10 - no matter what number of rows are specified in the first clause. What could I be missing? Could there be a typo in the statement (please see code below)? Thanks, Joe
CodeComment
-
V5R3 SQL Question
Does anyone know if the "Row_Number()" function or equivalent is available in V5R3?? I am working on an ASP.NET application that allows users to "Page" through a physical file with a few hundred thousand records (I will display about 10 to 15 records per page). What I want to do is have the SQL statement only return the 10 to 15 records that I need for each "page" (just like coding a "page at a time" sub file). One work around I found was using the "Fetch" Keyword to limit my results to a set number.....but the drawback as you know is that once the user "pages" to the end of the defined dataset, they would have to enter new search criteria to advance beyond that dataset (Example: Select * From Table Name Fetch First 100 rows Only). I found the SQL "row_number" function and that would work perfectly.......except I think it may only be available on V5R4?? Does anyone know of any SQL function available in V5R3 that would accomplish this task??......or any work arounds that you can think of?? I have tried the following but it is not generating the correct results: SELECT RRN(IT), OCCUP, OCCDEC, CSTITL FROM (SELECT OCCUP,OCCDEC, CSTITL FROM MYLIB/FILE WHERE OCCUP = 'C5' ORDER BY CSTITL) AS IT WHERE RRN(IT)>=1 AND RRN(IT)<=10 This statement returns blank b/c the actual rrn's of the selected records are above 10.......I thought this statement would "re-number" the results Ex. 1,2,3, but it instead returns the actual rrn for each record. Any help would be greatly appreciated. Thanks in advance. ScottComment
Comment