Re: SQL and retrieving the next xx rows

<snip>
So we'd want to say:

select.... where ordernum >= searchorder

but only have it select the next 100 orders (mainly because if we
didn't,
the search would take forever if there were a bazillion orders in the
database).
</snip>

Do you mean the following? This function loads a single page of a
subfile for the number of records per page. Note the for. I think that
is what you are looking for. Hopefully the forum won't mangle the code.

* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
* LoadSubfile
* Load subfile.
* Input - None
* Out - None
* Returns - None.
* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
p LoadSubfile...
p b
d pi
/Free

Exec Sql Declare C1 Scroll Cursor For
Select TDFILENAME,
TDLIBNAME,
TDACTIVE,
TDSRVPGM,
TDSRVLIB,
TDSRVPROC
From TGCTRLD
Where TDFILENAME >= :O_CURPOSTO
Order By TDFILENAME,
TDLIBNAME;

Exec Sql Close C1;

Exec Sql Open C1;
If SqlState <> cSqlStateOk;
ERRH_Throw('CPF9898' :
cCPFMessageFileName :
'Open of Cursor C1 failed');
EndIf;

ClearSubfile();
O_CURPOSTO = LoadOnePage();

Return;

/End-Free
p e
* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
* LoadOnePage
* Load one page of subfile records.
* Input - None
* Out - None
* Returns - Key of first record.
* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
p LoadOnePage...
p b
d pi Like(StdNam)

d RecordsRead...
d s Like(StdInt)
d NumberToLoad...
d s Like(StdInt)
d x...
d s Like(StdInt)
d SaveFirstKey...
d s Like(StdNam)
/Free

g_RecordsLoadedOnPage = 0;

NumberToLoad = cRecordsPerPage; // SQL cannot use constant.
Exec Sql Fetch Next
From C1
For :NumberToLoad Rows
Into :g_dsRecordIn;
Select;
When SqlState = cSqlStateNoRow;
RecordsRead = 0;
When SqlState <> cSqlStateOk;
ERRH_Throw('CPF9898' :
cCPFMessageFileName :
'Fetch from cursor C1 failed! Sql State = ' +
SqlState);
Other;
RecordsRead = SQLERRD(3);
EndSl;

SaveFirstKey = g_dsRecordIn(1).FileName;

For x = 1 To RecordsRead;
WriteSubfileRecord(x);
EndFor;

// If not EOF, check if more records.
If RecordsRead <> cRecordsPerPage;
DisplaySubfileEnd = cTrue;
Else;
Exec Sql Fetch Next
From C1
For 1 Rows
Into :g_dsRecordIn;
Select;
When SqlState = cSqlStateNoRow;
DisplaySubfileEnd = cTrue;
When SqlState <> cSqlStateOk;
ERRH_Throw('CPF9898'
:
cCPFMessageFileName
:
'Fetch from cursor C1 failed! Sql State = ' +
SqlState);
Other;
DisplaySubfileEnd = cFalse;
Exec Sql Fetch Prior
From C1
For 1 Rows
Into :g_dsRecordIn;
EndSl;
EndIf;

// If count of records loaded on page is zero and records in
// subfile is zero, then no records found to load.
If g_RecordsLoadedOnPage = 0;
If g_RecordsLoadedInSubfile = 0;
MSGF_SendProgramMessage('UXV0026' :
cXVMessageFileName :
' ' :
cMainProcedureName :
g_dsSystem.ModuleName:
g_ProgramName );
Else;
// No records found to load.
MSGF_SendProgramMessage('UXV0027' :
cXVMessageFileName :
' ' :
cMainProcedureName :
g_dsSystem.ModuleName:
g_ProgramName );
EndIf;
EndIf;

// Set position of sub-file to be last record loaded.
H_SFPOS = g_SubfileRecordNumber;

Return SaveFirstKey;

/End-Free
p e