Hi,
I want to put here my question which was already partially discussed (on my own…) on this topic.
But as it is more specific than the above topic, I created a new one here (with a more explicit title).
The aim is to implement a good ‘applyLimit’ function (on a new CommandBuilder for IBM DB’s).
As IBM DB’s don’t implement an offset on limits, the trick is to use the IBM intern ROW_NUMBER() function to retrieve … the row number as a new query field.
So, it’s quite easy to filter results with a WHERE <the_row_number> BETWEEN <offset + 1> AND <limit + offset>.
But it fails when you want to order AND filter (with GridView for instance) because the ROW_NUMBER() (which is really ‘ROW_NUMBER() OVER()’) asks the ‘ORDER BY’ part as the OVER() parameter…
So the query should be builded in the applyLimit function as : ‘SELECT t.*, ROW_NUMBER() OVER(ORDER BY id DESC) AS row FROM (<the sql query> ORDER BY id DESC) WHERE row BETWEEN <x> AND <y>’.
A simple query on a table without relations is ok as the ‘createFindCommand’ is part of the new CommandBuilder.
But when it involves relations, the applyLimit function is called from outside the CommandBuilder and it becomes less easy to build the query correctly (in the createFindCommand, we have access to the CdbCriteria object).
As I don’t know the whole framework (especially the classes to build the joins), can someone help me?
Many thanks.