The subfile loading method is the same as for a loading by static query, but the query has a different Adelia structure.
A set of SQL instructions is also processed automatically such as the cursor declaration, and the reading of this cursor for subfile loading.
The Adelia instruction is unique:
SQL_SFL_D Query n SeriesVarId WITH SeriesSubstitutionVarId
Where
Query |
: Name of the variable containing the query to be executed. |
n |
: Number of the subfile to be loaded. |
SeriesVarId |
: Names of the screen variables to be loaded, separated by a space. |
SeriesSubstitutionVarId |
: Any substitution variables which may follow, separated by a space. |
The SQL query must have been composed beforehand and stored in a variable.
Adelia then declares a working file (SQL cursor) in which the query results are stored.
This file is then automatically re-read when loading the subfile.
For example
QUERY = *BLANK |
: Resets working field used for SQL query |
IF ZQUE1 <> *BLANK |
|
QUERY = 'SELECT PCOMAT,PLINAM...FROM HP#PERP WHERE ' // ZQUE1 |
|
|
: The query to execute is loaded in the working |
|
: field and field ZQUE1 contains the selection |
|
: conditions specified. |
SQL_SFL 1 QUERY ZCOMAT ZLINAM |
|
|
: Designates subfile *1 and loads fields |
|
: ZCOMAT ZLINAM according to the previous |
|
: query in the QUERY field. |
* |
: Subfile loading loop. Adelia automatically |
CLEAR 1 |
: reads the working file (cursor) resulting |
MANAGE_SFL 1 |
: from the SQL execution. |
UPD_SFL |
|
END_MANAGE_SFL |
|
* |
|
A possible variation using SQL syntax consists in defining the query in such a way as to replace a comparison value on execution. Such a variable to substitute in the SQL query must always be qualified by the character "?".
For example
Query without substitution:
A query: '.....WHERE PCOTPA = ' // '''' // ZCOTPA // ''''
could be replaced with: '....WHERE PCOTPA = ? '**
Query with substitution. The Adelia instructions would then become as follows:
QUERY = 'SELECT PCOMAT, PLINAM .. WHERE PCOTPA = ? '
SQL_SFL_D QUERY 1 ZCOMAT ZLINAM ... WITH ZCOTPA
where
ZCOTPA = Substitution variable