Hi Guys, I have an issue with using setFetchOffset & setFetchLimit together with SQL Server 2008 R2.
Basically the code looks something like this: SelectQuery query = getPrototypeQuery().queryWithParameters(params, true); query.setFetchOffset(offset); query.setFetchLimit(limit); List<ConsentForm> result = getDataContext().performQuery(query); I would have thought that given for example offset 10 and limit 20 I could get a subset of the data in the database. Yet the generated code that I get from the logs is as follows keeping in mind I am using ordering and distinct: Page 1: Offset 0 Limit 25 SELECT DISTINCT TOP 25 t0.DateSigned, t0.CustomerName, t0.DocsFolder, t0.ExpiryDate, t0.CustomerID, t0.CreatedDate, t0.LocationAddress, t0.RecordID, UPPER(t0.DateSigned) FROM dbo.ConsentForms t0 WHERE t0.CustomerID = ? ORDER BY UPPER(t0.DateSigned) [bind: 1->CustomerID:8] === returned 25 rows. - took 26 ms The above works as expected however the code appears strange. Page 2: Offset 25 Limit 25 SELECT DISTINCT TOP 25 t0.DateSigned, t0.CustomerName, t0.DocsFolder, t0.ExpiryDate, t0.CustomerID, t0.CreatedDate, t0.LocationAddress, t0.RecordID, UPPER(t0.DateSigned) FROM dbo.ConsentForms t0 WHERE t0.CustomerID = ? ORDER BY UPPER(t0.DateSigned) [bind: 1->CustomerID:8] === returned 0 rows. - took 20 ms This already stops working Page: 3: Offset 50 Limit 25 SELECT DISTINCT TOP 25 t0.DateSigned, t0.CustomerName, t0.DocsFolder, t0.ExpiryDate, t0.CustomerID, t0.CreatedDate, t0.LocationAddress, t0.RecordID, UPPER(t0.DateSigned) FROM dbo.ConsentForms t0 WHERE t0.CustomerID = ? ORDER BY UPPER(t0.DateSigned) [bind: 1->CustomerID:8] === returned 0 rows. - took 20 ms. Basically the first page appears to be working fine, but nothing afterwards. I am not certain if I am using the combination of setFetchOffset and setFetchLimit in a correct manner! Any assistance would be appreciated. Thank you! Gary
