%SQL meta-SQL element

Syntax

%SQL(SQL_ID [, paramlist])

where paramlist is a list of arguments that are used for dynamic substitutions at runtime, in the form:

arg1 [, arg2]. . .

Description

Use the %SQL construct for common SQL fragments that you have already defined and want to reuse, substituting additional values dynamically. SQL_ID is the name of a SQL definition created using either Application Designer or the StoreSQL function.

You can only nest up to 10 %SQL statements at a time.

Note:

This meta-SQL construct is not implemented for COBOL. A SQL definition is not the same as the SQL object that is instantiated from the SQL class at runtime. A SQL definition is created either using Application Designer at design time, or using the StoreSQL function. A SQL object is instantiated at runtime from the SQL class, and has methods and properties associated with it like any other object.

When a specified SQL definition has more than one version, the database type always takes precedence.

If one or more versions of a SQL definition are found for the database type of the current database connection, and if any of the versions have an effective date less than or equal to the value returned for %AsOfDate, the most recent version is used.

If no versions are found for the current database type, or if all of the versions have effective dates greater than the value returned for %AsOfDate, the system looks for an effective version of the SQL definition under the database type Generic.

If no version is found, an error occurs.

Parameters

Parameter Description

Parameter	Description
SQL_ID	Specify the name of an existing SQL definition.
paramlist	Specify a list of arguments for dynamic substitutions at runtime. The first argument replaces all occurrences of %P(1) in the referenced SQL definition, the second argument replaces %P(2), and so forth. You can specify up to 99 arguments. Note: For PeopleCode, the %P should not be contained in quotation marks. `'%P(2)'` is considered to be a literal, and so isn't replaced at runtime.

SQL_ID

Specify the name of an existing SQL definition.

paramlist

Specify a list of arguments for dynamic substitutions at runtime. The first argument replaces all occurrences of %P(1) in the referenced SQL definition, the second argument replaces %P(2), and so forth. You can specify up to 99 arguments.

Note: For PeopleCode, the %P should not be contained in quotation marks. '%P(2)' is considered to be a literal, and so isn't replaced at runtime.

Example

In the following example, the SQL definition MY_SQL was created in Application Designer to be the following:

%P(1).EFFDT = (SELECT MAX(EFFDT) FROM ...)

In the following example, the %SQL statement is dynamically generated:

UPDATE PS_TEMP 
SET ... 
WHERE ... 
AND %SQL(MY_SQL, PS_TEMP)

The previous example resolves to the following:

UPDATE PS_TEMP 
SET ... 
WHERE ... 
AND PS_TEMP.EFFDT = (SELECT MAX(EFFDT) FROM ...)

Application Engine Considerations

Application Engine programs use the current date to compare with the effective date, not the date returned by %AsOfDate.

Special SQL Characters

The following meta-SQL variables can be used as part of the %SQL construct to represent special characters as SQL parameters.

Meta-SQL Variable	Description
%Comma	Represents a single comma.
%LeftParen	Allows you to pass a left parenthesis character to a %P() variable, without closing the SQL object.
%RightParen	Allows you to pass a right parenthesis character to a %P() variable, without closing the SQL object.
%Space	Represents a space.