SmartClient Version: v9.1p_2014-09-17/PowerEdition Deployment (built 2014-09-17)
I have seen several similar posts in the Forum but this sure seems like a bug.
I have a DataSource with this fetch operation definition:
From the debug log this produces the correct SELECT statement on a
grid.fetchdata() call
However in the next step it is fetching by rows 1 to 75, and introducing a new order clause, ORDER by idSetup_Waiver, the PK of the base table. This is the windowing / paging function.
So it does this:
So the ROW_NUMBER() of paging is overriding the complicated ORDER BY clause of the DataSource.
I know there is a possible work around by turning off paging. But of course that would impact fetch performance for the user.
I can't see how using a SortSpecifier object I can define a client side ORDER specification using my complicated clause. So that doesn't seem like a solution when using the setSortBy on a DSRequest.
Is this a bug?
I have seen several similar posts in the Forum but this sure seems like a bug.
I have a DataSource with this fetch operation definition:
Code:
<operationBinding operationType="fetch"> <selectClause> Authorized, CT_on_change, CT_on_create, UREQ.FullName as RequestedBy, WaiverRun.WCorner, Path_Group, Path_GroupType, StartPt_Chip_Inst_Path, EndPt_Chip_Inst_Path, StartPoint, EndPoint, STA_SW_State, WaiverRun.STA_WaiverRunState, Comment, WaiverRun.Waiver_Comment, idSetup_Waiver </selectClause> <tableClause> STA_Waiver_Run as WaiverRun JOIN STA_Setup_Waiver ON STA_Setup_Waiver.FK_STA_Waiver_Run = WaiverRun.PK_STA_Waiver_Run JOIN Users as UREQ on UREQ.PK_Users_Userid = STA_Setup_Waiver.Requestor </tableClause> <orderClause> CASE WHEN LOWER(STA_SW_State) = 'dangling' THEN '1' WHEN LOWER(STA_SW_State) = 'rejected' THEN '2' WHEN LOWER(STA_SW_State) = 'invalid' THEN '3' WHEN LOWER(STA_SW_State) = 'pending' THEN '4' ELSE '5' END ASC </orderClause> </operationBinding>
grid.fetchdata() call
Code:
<operationBinding operationType="fetch"> <selectClause> Authorized, CT_on_change, CT_on_create, UREQ.FullName as RequestedBy, WaiverRun.WCorner, Path_Group, Path_GroupType, StartPt_Chip_Inst_Path, EndPt_Chip_Inst_Path, StartPoint, EndPoint, STA_SW_State, WaiverRun.STA_WaiverRunState, Comment, WaiverRun.Waiver_Comment, idSetup_Waiver </selectClause> <tableClause> STA_Waiver_Run as WaiverRun JOIN STA_Setup_Waiver ON STA_Setup_Waiver.FK_STA_Waiver_Run = WaiverRun.PK_STA_Waiver_Run JOIN Users as UREQ on UREQ.PK_Users_Userid = STA_Setup_Waiver.Requestor </tableClause> <orderClause> CASE WHEN LOWER(STA_SW_State) = 'dangling' THEN '1' WHEN LOWER(STA_SW_State) = 'rejected' THEN '2' WHEN LOWER(STA_SW_State) = 'invalid' THEN '3' WHEN LOWER(STA_SW_State) = 'pending' THEN '4' ELSE '5' END ASC </orderClause> </operationBinding>
However in the next step it is fetching by rows 1 to 75, and introducing a new order clause, ORDER by idSetup_Waiver, the PK of the base table. This is the windowing / paging function.
So it does this:
Code:
=== 2015-01-30 19:20:49,553 [0-40] DEBUG SQLDataSource - [builtinApplication.BCG_STA_Setup_Waiver_fetch] SQL windowed select rows 0->75, result size 75. Query: SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY idSetup_Waiver) AS rowID FROM (SELECT TOP 100 PERCENT Authorized, CT_on_change, CT_on_create, UREQ.FullName as RequestedBy, WaiverRun.WCorner, Path_Group, Path_GroupType, StartPt_Chip_Inst_Path, EndPt_Chip_Inst_Path, StartPoint, EndPoint, STA_SW_State, WaiverRun.STA_WaiverRunState, Comment, WaiverRun.Waiver_Comment, idSetup_Waiver FROM STA_Waiver_Run as WaiverRun JOIN STA_Setup_Waiver ON STA_Setup_Waiver.FK_STA_Waiver_Run = WaiverRun.PK_STA_Waiver_Run JOIN Users as UREQ on UREQ.PK_Users_Userid = STA_Setup_Waiver.Requestor WHERE (WaiverRun.FK_BCG_UIWaiverMaster=78) ORDER BY CASE WHEN LOWER(STA_SW_State) = 'dangling' THEN '1' WHEN LOWER(STA_SW_State) = 'rejected' THEN '2' WHEN LOWER(STA_SW_State) = 'invalid' THEN '3' WHEN LOWER(STA_SW_State) = 'pending' THEN '4' ELSE '5' END ASC ) x) y WHERE y.rowID BETWEEN 1 AND 75
I know there is a possible work around by turning off paging. But of course that would impact fetch performance for the user.
I can't see how using a SortSpecifier object I can define a client side ORDER specification using my complicated clause. So that doesn't seem like a solution when using the setSortBy on a DSRequest.
Is this a bug?
Comment