%Join meta-SQL element

Syntax

%Join({COMMON_KEYS | COMMON_FIELDS}, join_recname [ correlation_id1], to_recname [ correlation_id2] [, override_field_list])

where override_field_list is an arbitrary-length list of fields to be substituted in the resulting text string, in the form:

field1 [, field2]. . .

Description

Use the %Join meta-SQL construct to dynamically build a Where clause joining one table to another. At runtime, the entire construct is replaced with a character string.

Note:

This meta-SQL construct is not implemented for COBOL. If date key fields are not marked as required in the record definition for either of the referenced tables in the %Join clause, a Null clause check is added to the date field comparison. This additional clause can have a significant impact on the execution time for the generated SQL statement.

Parameters

Parameter	Description
{COMMON_KEYS | COMMON_FIELDS}	Use COMMON_KEYS to specify that all common primary key fields are used in constructing a Where clause; use COMMON_FIELDS to specify all common fields, not just key fields. You can select either COMMON_KEYS or COMMON_FIELDS.
join_recname	Specify the name of the record to be joined. This can be a bind variable, a record object, or a record name in the form recname. You can't specify a RECORD. recname, a record name in quotation marks, or a table name.
correlation_id1	Identify the correlation ID used to relate the record specified by join_recname and its fields.
to_recname	Specify the name of the record to be joined to. This can be a bind variable, a record object, or a record name in the form recname. You can't specify a RECORD. recname, a record name in quotation marks, or a table name.
correlation_id2	Identify the correlation ID used to relate the record specified by to_recname and its fields.
override_field_list	Specify a list of fields that you do not want used in the join. For example, if fields A, B, and C were common to two records, and you didn't want to join on C, list C as an override_field.

Example

Here is an example:

%Join(COMMON_KEYS, PSAESECTDEFN ABC,  PSAESTEPDEFN XYZ) 

The example code results in the following being generated:

ABC.AE_APPLID = XYZ.AE_APPLID  
AND ABC.AE_SECTION = XYZ.AE_SECTION  
AND ABC.DBTYPE = XYZ.DBTYPE  
AND ABC.EFFDT = XYZ.EFFDT

Here's another example:

%Join(COMMON_FIELDS, PSAEAPPLDEFN ABC,  PSAESECTDEFN XYZ) 

The second example results in the following being generated:

ABC.AE_APPLID = XYZ.AE_APPLID  
AND ABC.DESCR = XYZ.DESCR 

However, you do not want to perform the join using the DESCR field because it's a long field. Instead use override_field, as shown in the following code:

%Join(COMMON_FIELDS, PSAEAPPLDEFN ABC,  PSAESECTDEFN XYZ, DESCR) 

This example results in the following being generated:

ABC.AE_APPLID = XYZ.AE_APPLID

You can also specify a value for a field. Suppose you want to join two tables, but not on the field C3. In addition, you would like to specify a value for C3. Your code could look like the following:

%Join(COMMON_FIELDS, MY_TABLE1 A, MY_TABLE2 B, C3) AND C3 = 'XX'