AnsweredAssumed Answered

Using Offset and Fetch First... Execute SQL to return first 50 records.. then the next 50 and so...

Question asked by AppGuy on May 12, 2014
Latest reply on May 21, 2014 by AppGuy

Title

Using Offset and Fetch First... Execute SQL to return first 50 records.. then the next 50 and so on..

Post

     What I would like to do is use the new feature in execute sql called offset and fetch first..  ( I think this would be the easiest and fastest way.)  

     Tables I am pulling from is TimeCards and EmployeeTime.  

     They are joined together by the TimeCards::__pk_ID --> EmployeeTime::_fk_TimeCards

     This is a hosted solution and due the the speed of sorting (pretty slow).  I need to limit the number of records that are shown at a time and then have a next and back button.

     The best way seems to be using this new method but I can't find much documentation on it yet..

     Documentation : Here

     I am hoping someone who is proficient in Execute SQL can lend me a hand.. :)

     How would I go about creating the next and back buttons? Do I just pass a parameter to the script?

     I would imaging something like this..

      

     I assume this would be the initial search...

SELECT * FROM TIMECARDS FETCH FIRST 50 ROWS ONLY  -  I would need to specify the search criteria somewhere..??  For instance..  on the List View I use - TimeCards::Approver and TimeCards::Date fields in the TimeCards Table

     Then - Next and back buttons.

SELECT * FROM TIMECARDS OFFSET 50 ROWS FETCH FIRST 50 ROWS ONLY  -  NEED HELP HERE..  ??  I assume I need a variable or parameter passed here to go next and back..??  not sure..

     Any help or suggestions would be great..  

     attached is a screenshot of the list view..

     Thanks,

     JP

Screen_Shot_2014-05-12_at_11.06.04_AM.png

Outcomes