Hi Glenn,

Cause bind variables are not supported to specify START AT value you may use a workaround like this:

select *
from (
    SELECT *,
           row_number() over (order by emp_id asc) as rnum
    from employee) as emp
where rnum between :startno and :startno + :topno

HTH,

René

My solution was to implement a Retrieval Threshold service.

1. Define a Preference "max number of rows allowed" and set it to your value.
   In my case, I use 50,000 rows.

2. In the SQLPREVIEW EVENT:
       Wrap a SELECT COUNT(*) INTO ll_rowcount FROM ( <your sql> );

       IF (ll_rowcount > Max_Rows_Allowed) THEN
           Set a forced END RETRIEVAL in the RetrieveEnd event when the max rowcount is reached
           OR
           Disallow the Retrieval and popup an error msg asking the user to change the parameters

       END IF


Later -

Olan

  

OK, you want the users to approved the next BLOCK of rows to be retrieved?

In the RETRIEVESTART event, set:   il_rows_retrieved = 0
                                                    il_rows_per_block = 1000

In the RETRIEVEROW event, set:  

il_rows_retrieved =il_rows_retrieved + 1
IF (il_rows_retrieved >= il_rows_per_block ) THEN
   il_rows_retrieved = 0
   ll_rc = MessageBox ("Get Next Set Of Rows?", "Do you want to retrieve the next block of " + &
                               string (il_rows_per_block ) + " rows?", YesNo!, Question!)

   IF (ll_rc = 1) THEN
       // Continue retrieving data
       ll_return = 0
   ELSE
       // Stop the retrieval
       ll_return = 1
   END IF

END IF

RETURN  ll_return

Thanks for your help.. unitl we do the upgrade to PB2019 abd SA71 - we can do this and its quite simple

(one thing we discovered is that the START AT 1 needs a space after even though followed by a (CR) - otherwise in SQLPREVIEW  it looks like START AT 1columnname, ...

in the DataWindow SQL

SELECT TOP :topno START AT 1 
propasset.propassetid,
propasset.propassetdesc,
...

FROM propasset
WHERE
...

ORDER BY ...;

in the SQL Preview event

----------------------------------------------------------

int li_rc

string ls_sql
string ls_expr

ls_sql = sqlsyntax

IF il_startrow <> 1 THEN

ls_expr = "START AT " + String(il_startrow)

ls_sql = inv_string.of_GlobalReplace(ls_sql, "START AT 1", ls_expr)

li_rc = this.SetSQLPreview(ls_sql)

END IF