Pagination Offset in Query JSON with SQL

[jason3w]

From what I can tell, setting the OFFSET dynamically inside the Query JSON with SQL is not possible. I've tried a number of different approaches. Should it be working, or is this not supported? Thanks

With static offset as "0"

With dynamic value returning "0"

[cjschmedt]

What kind of SQL server are you using?

For MSSQL this worked for me:
SELECT *
FROM TABLE
...
OFFSET {{ collectionView.selectedPageIndex }}*10 ROWS
FETCH NEXT 10 ROWS ONLY;

Within the query library, I receive an error "The number of rows provided for a OFFSET clause must be an integer.", but within the app it works fine.

[jason3w]

It's a postgres database.
Were you achieving this in the "Query JSON with SQL" query or a standard DB query?

[cjschmedt]

Using standard DB query (MSSQL connector).

I am not sure why you would need to go with "Query JSON with SQL" when querying a SQL database (Disclaimer: I know nothing about postgreSQL nor "Query JSON with SQL"), rather than using the standard postgresql connector.

[jason3w]

From what i can tell, running the main query (query1) once and then using the "Query JSON with SQL" resource is much faster for filtering query1 than running the main query again and again to fetch the filtered data.

[Kabirdas]

Hey folks! This is actually a known bug, and I've bumped it with the dev team, thanks for bringing it up here!

In the meantime can you try adding the following as a query transformer?

return data.slice({{offset.value}}, {{offset.value + limit.value}})