%FirstRows meta-SQL element

Syntax

%FirstRows(n)

Description

The %FirstRows meta-SQL variable is replaced by database-specific SQL syntax to optimize retrieval of n rows. Depending on the database, this variable optimizes:

  • The query path.

  • The number of rows returned.

  • The number of rows returned per fetch buffer.

Parameters

Parameter Description

n

Specify the number of rows to optimize retrieval for.

Example

The following code checks for the existence of a row:

&SQL = CreateSQL("select %firstrows(1) 'x' from PS_EXAMPLE where COL1 = :1",⇒
 &temp);

The following populates a 10-element array: 

&SQL = CreateSQL("select %firstrows(10) COL2, COL3 from PS_EXAMPLE_VW where COL1 =⇒
 :1", &temp);

Considerations Using %FirstRows

Consider the following when using %FirstRows:

  • Using %FirstRows does not mean only the first n rows are returned.

    It means that the SQL is optimized for the first n rows where the platform supports it. More rows might be returned, depending on the platform.

  • It is the application's responsibility to stop fetching when enough rows have been returned.

  • This meta-SQL variable is not implemented for COBOL or dynamic view SQL.

  • Do not use this meta-SQL variable if the application might require more than n rows fetched.

    The results of fetching more than n rows varies by platform. Some return the extra rows, but performance may be suboptimal. Others return the message "ROW NOT FOUND".

  • Place this meta-SQL variable between the Select statement that begins the SQL statement and the Select List statement.

    Do not use it in subqueries, views, Insert/Select statements, and so on. Do not use a wildcard (*) with the Select List statement.

  • Do not use this meta-SQL variable with Distinct statements, because the code SELECT TOP 1 DISTINCT fails on Microsoft SQL Server.

  • This meta-SQL variable is implicitly embedded in all Select statements for SQLExecs for all platforms except Oracle.