Unofficial Content

Below we explain how a SQL sentence ( which is taken as an example ), would be generated when Server Paging is performed.

SQL Server

For SQL Server version 2012 or higher (SAC #37451):

SELECT [MaterialId], [MaterialDsc] 
FROM [Material] WITH (NOLOCK) ORDER BY [MaterialId]
OFFSET ? ROWS FETCH NEXT CAST((SELECT CASE WHEN ? > 0 THEN ? ELSE 1e9 END) AS INTEGER) ROWS ONLY

For previous version of SQL Server:

SELECT * FROM (SELECT  [MaterialId], [MaterialDsc], ROW_NUMBER() OVER ( ORDER BY [MaterialId] ) AS GX_ROW_NUMBER
FROM [Material] WITH (NOLOCK)) AS GX_CTE 
WHERE GX_ROW_NUMBER BETWEEN ? AND ? OR ? < ? AND GX_ROW_NUMBER >= ?

This sentence was optimized since GeneXus 15  (SAC #42635)

SELECT * FROM (SELECT [MaterialId], [MaterialDsc], ROW_NUMBER() OVER ( ORDER BY [MaterialId] ) AS GX_ROW_NUMBER 
FROM [Material] WITH (NOLOCK)) AS GX_CTE 
WHERE GX_ROW_NUMBER >= ? AND GX_ROW_NUMBER <= (CASE WHEN (? < ?) THEN CAST(0x7FFFFFFFFFFFFFFF AS bigint) else ? end) 

Oracle

For Oracle 12:

SELECT MaterialId, MaterialDsc 
FROM Material WITH (NOLOCK)
ORDER BY MaterialId
OFFSET ? ROWS FETCH NEXT CAST((SELECT CASE WHEN ? > 0 THEN ? ELSE 10e15 END) AS INTEGER) ROWS ONLY

For the previous version:

SELECT * FROM ( SELECT GX_CTE.*, ROWNUM GX_ROW_NUMBER
FROM (SELECT  MaterialId, MaterialDsc FROM Material ORDER BY MaterialId) GX_CTE)
WHERE GX_ROW_NUMBER BETWEEN ? AND ? OR ? < ? AND GX_ROW_NUMBER >= ?

PostgreSQL

SELECT MaterialId, MaterialDsc FROM Material ORDER BY MaterialId  OFFSET ? LIMIT CASE WHEN ? > 0 THEN ? ELSE 10e15 END

MySQL

SELECT `MaterialId`, `MaterialDsc` FROM `Material` ORDER BY `MaterialId`  LIMIT ?, ?

iSeries

SELECT * FROM (SELECT  TrnA3, TrnA2, TrnA1, ROW_NUMBER() OVER ( ORDER BY TrnA1 ) AS GX_ROW_NUMBER FROM TrnA) AS GX_CTE 
WHERE GX_ROW_NUMBER BETWEEN ? AND ? OR 0 + ? < ? AND GX_ROW_NUMBER >= ?

 

 

Last update: February 2024 | © GeneXus. All rights reserved. GeneXus Powered by Globant