customSQL and paging

[tmoes]

Hello,
how can I use paging with customSQL? I always get back only 75 rows, I can not scroll further down.

Without the use of "customSQL" I get all the lines delivered by scrolling.

<operationBinding requiresAuthentication="true" operationType="fetch">
 	<customSQL>
		SELECT $defaultSelectClause FROM $defaultTableClause WHERE $defaultWhereClause AND  (B2B_FH.mandant = $session.mand)
	</customSQL>
</operationBinding>

thanks for your help.

[Isomorphic]

Be more selective in your override - you only need to override the <whereClause>, not the whole SQL query. If you override just what you need, paging, sorting etc will still be automatic. See the Large ValueMap sample for an example.

[tmoes]

great !!!!!!!!!!!

[jay.l.fisher]

What if I need to use customSQL, for example, to use a SQL WITH clause? How can I implement paging with a fully custom SQL statement?

[Isomorphic]

Can you show the query you had in mind?

If you in fact have to use <customSQL> and can't break it up, you have the dsRequest and hence $dsRequest.startRow/endRow available to you to implement paging if you know how to write the SQL for a ranged fetch for your database.

Alternatively, you can fetch all the data from the DB and slice out the request range in Java. Less efficient, obviously.

[jay.l.fisher]

Here is an example where I'm using a WITH clause, which has to precede the SELECT clause. Since the <selectClause> automatically includes "SELECT" I can't precede it with a WITH clause . There may be another way to formulate the same result but the WITH clause makes it simple.

with POItem as (select IONO,
	sum(CASE WHEN IERR='C' OR ITQR>IQTY THEN 0 ELSE IQTY-ITQR END)  AS UnitsOpen,
	sum((CASE WHEN IERR='C' OR ITQR>IQTY THEN 0 ELSE IQTY-ITQR END) * IRET)  AS RetailOpen,                     
	sum((CASE WHEN IERR='C' OR ITQR>IQTY THEN 0 ELSE IQTY-ITQR END) * ILNC)  AS LandedCostOpen,
	sum(ITQR) as UnitsReceived,                        
	sum(IRET*ITQR) as RetailReceived,
	sum(ILNC*ITQR) as LandedCostReceived,  
	sum(IQTY) as UnitsOrdered,                        
	sum(IRET*IQTY) as RetailOrdered,
	sum(ILNC*IQTY) as LandedCostOrdered           
	from ippoitm#if($httpParameters.FileGroupSuffix)$rawValue.httpParameters.FileGroupSuffix#end 
	group by iono)                         
select $defaultSelectClause, 
UnitsOrdered, RetailOrdered, LandedCostOrdered, 
UnitsReceived, RetailReceived, LandedCostReceived,
UnitsOpen, RetailOpen, LandedCostOpen 
FROM ippohdr#if($httpParameters.FileGroupSuffix)$rawValue.httpParameters.FileGroupSuffix#end 
	left outer join POItem on HONO=IONO   
where $defaultWhereClause

[jay.l.fisher]

I turns out I can use a different technique that is just as simple. Instead of WITH I can create the same summary table right in the JOIN so I can put it in the tableClause like this.

<tableClause>
	ippohdr#if($httpParameters.FileGroupSuffix)$rawValue.httpParameters.FileGroupSuffix#end 
	LEFT OUTER JOIN 
		(select IONO,
		sum(CASE WHEN IERR='C' OR ITQR>IQTY THEN 0 ELSE IQTY-ITQR END)  AS UnitsOpen,
		sum((CASE WHEN IERR='C' OR ITQR>IQTY THEN 0 ELSE IQTY-ITQR END) * IRET)  AS RetailOpen,                     
		sum((CASE WHEN IERR='C' OR ITQR>IQTY THEN 0 ELSE IQTY-ITQR END) * ILNC)  AS LandedCostOpen,
		sum(ITQR) as UnitsReceived,                        
		sum(IRET*ITQR) as RetailReceived,
		sum(ILNC*ITQR) as LandedCostReceived,  
		sum(IQTY) as UnitsOrdered,                        
		sum(IRET*IQTY) as RetailOrdered,
		sum(ILNC*IQTY) as LandedCostOrdered           
		from ippoitm#if($httpParameters.FileGroupSuffix)$rawValue.httpParameters.FileGroupSuffix#end 
		group by iono) POItem 
	ON hono=iono
</tableClause>