CHAIN_SQL

Warning: In the case of ADELIA programs, this instruction is only compatible with the RPG generator.

Section for use

All

Syntax

CHAIN_SQL SeriesEntities SelectedColumns AdeliaCondition Parameter

Description

This instruction reads the entity or entities SeriesEntities, obeying the (optional) condition AdeliaCondition and inserts the values in the entity fields into the file variables.

It is equivalent to the SQL instruction: "Select into SeriesEntityVariables from SeriesEntities where AdeliaCondition".

CorrelationName is used to prevent ambiguity arising between two or more fields which have the same name but belong to different entities.

If a correlation name is used, the correlation name alone will be used in AdeliaCondition.

A correlation name is written with a single character. It is often used to shorten the names of the entities.

By default, the instruction returns the values of all the entities' columns.

However, it is possible to retrieve only the values which are specified in SelectedColumns.

It is also possible to retrieve the following elements in HostVarId:

• • the number of rows which correspond to the condition (COUNT function),

  • the number of distinct rows for the EntityVarId column corresponding to the condition (DISTINCT_COUNT function),

  • the sum (SUM function) or the average (AVERAGE function) of the values of the (numeric) EntityVarId column for the rows corresponding to the condition,

  • the maximum value (MAX function) or the minimum value (MIN function) for the EntityVarId column of one of the rows corresponding to the condition.

Warning: On AS/400, if the condition causes more than one record (or row) to be selected, the read operation will fail and the fields in the entities may be modified.

On PC SQL databases (DB2, Oracle, SQL Server, ODBC), if the condition causes more than one record (or row) to be selected, the read operation will complete successfully (*SQLCODE = *NORMAL) but only the first record will be returned.

The *MR parameter allows you to generate read implicit management rules that are linked to the entity, even if the program is generated without the option to generate implicit management rules.

The *NO_MR parameter allows you not to generate read implicit management rules that are linked to the entity, even if the program is generated with the option to generate implicit management rules.

After this instruction has been run, it is possible to test the SQL return code with the *SQLCODE reserved word.

Example

CHAIN_SQL CLIENT, ORDER A *COND(A.ORDER_ID=1028 and CCLIENT_ID=A.OCLIENT_ID)

* Reads the name of the insured person corresponding to an insured number

CHAIN_SQL INSURED *COL(IINSURED_NAME) *COND(IINSURED_ID = :ZIINSURED_ID)

*Retrieves the number of insured persons in Paris in the INSURED_NBR variable

CHAIN_SQL INSURED *COL(COUNT(*) :INSURED_NBR) *COND(INS_CITY = 'PARIS')

*Retrieves the maximum value of the client sales figure

CHAIN_SQL CLIENT *COL(MAX(CLI_SALES) :ZZ_MAX_SALES)

↑ Top of page