Using Meta-SQL and PeopleCode

This chapter provides an overview of Application Engine meta-Structured Query Language (SQL) and discusses how to:

Click to jump to parent topicUnderstanding Application Engine Meta-SQL

Application Engine meta-SQL is divided into the following categories:

Note. Some meta-SQL elements can be used only in Application Engine programs, some can be used both in Application Engine programs and in other environments, and some cannot be used in Application Engine programs at all. This PeopleBook discusses only meta-SQL elements that can be used in Application Engine. You can find a complete reference to all PeopleSoft meta-SQL elements in PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

See Also

Using Application Engine Meta-SQL

Understanding Meta-SQL

Click to jump to parent topicUsing PeopleCode in Application Engine Programs

This section provides an overview of PeopleCode and Application Engine programs and discusses how to:

Click to jump to top of pageClick to jump to parent topicUnderstanding PeopleCode and Application Engine Programs

Inserting PeopleCode into Application Engine programs enables you to reuse common function libraries and improve performance. In many cases, a small PeopleCode program used instead of Application Engine PeopleCode is an excellent way to build dynamic SQL, perform simple If/Else edits, set defaults, and perform other tasks that do not require a trip to the database.

Scope of Variables

This table presents the different types of variables typically used in Application Engine programs and their scope:

Type of Variable

Scope

Comments

State record (work record)

Transaction (unit of work)

Using a work record as your Application Engine state record means that the values in the work record cannot be committed to the database. Commits happen as directed, but any values in work records are not retained after a commit.

State record (database record)

Application Engine program

Using a database record as your Application Engine state record preserves the values in the state record on commit, and the committed values are available in the event of a restart.

Local PeopleCode variables

PeopleCode program

Local PeopleCode variables are available only for the duration of the PeopleCode program that is using them.

Global PeopleCode variables

Application Engine program

Global PeopleCode variables are available during the life of the program that is currently running. Any global PeopleCode variables are saved when an Application Engine program commits and checks points; therefore, they are available in the event of a restart.

Component PeopleCode variables

Application Engine program

Component PeopleCode variables act like global variables in Application Engine.

Action Execution Order

A step can contain only one PeopleCode action because no other types of actions are required within a step in conjunction with a PeopleCode action (or program). If you include other actions with your PeopleCode action within the same step, keep in mind the hierarchy when you run it.

With PeopleCode actions, Application Engine runs the PeopleCode program before the SQL, Call Section, or Log Message actions, but a PeopleCode program runs after any program flow checks.

Because multiple action types exist, they must execute in agreement within a system; therefore, the order in which actions execute is significant. At runtime, actions defined for a given step are evaluated based on their action type. All of the action types exist within a strict hierarchy of execution. For example, if both a Do When action and a PeopleCode action exist within a given step, then the Do When action always runs first.

The following example shows the sequence and level of execution for each type of action:

Example of action execution hierarchy

Click to jump to top of pageClick to jump to parent topicDeciding When to Use PeopleCode

Application Engine is not intended to run programs that include only PeopleCode actions. The primary purpose of Application Engine is to run SQL against your data.

Use PeopleCode primarily for setting If, Then, Else logic constructs, performing data preparation tasks, and building dynamic portions of SQL statements; rely on SQL to complete the bulk of actual program processing. Also use PeopleCode to reuse previously developed online logic. PeopleCode is the tool to use to take advantage of new technologies such as component interfaces and application classes.

Most programs must verify that a certain condition is true before they run a particular section. For example, if the hourly wage is less than or equal to X, do Step A; if not, fetch the next row. In certain instances, you must modify variables that exist in a state record. PeopleCode enables you to set state record variables dynamically.

Avoid rowset processing in an Application Engine program. Loading data into a rowset can use a significant amount of memory, which this formula approximates:

mem = nrows * (row overhead + nrecords * (rec overhead + nfields * (field overhead) + average cumulative fielddata))

where

Using this formula, a rowset containing 500,000 rows with one record per row, 50 fields, and 200 bytes per field would require approximately 2.3 gigabytes of memory.

Click to jump to top of pageClick to jump to parent topicConsidering the Program Environment

When writing or referencing PeopleCode in a PeopleCode action, you must consider the environment in which the Application Engine program runs. Environment indicates the differences between online and batch modes. Application Engine programs usually run in batch mode; consequently, your PeopleCode cannot access pages or controls as it can while running in online mode. Any PeopleCode operations that manipulate pages will not run successfully. Even if you invoke your Application Engine program online from a record or a page using the CallAppEngine PeopleCode function, the Application Engine PeopleCode still does not have direct access to component buffers.

Any record field references that appear in a PeopleCode action can refer only to fields that exist on an Application Engine state record. Component buffers, controls, and so on are still inaccessible even if you define the page records as state records in the Program Properties dialog box. An Application Engine program can access only state records or other objects you create in PeopleCode.

However, you do have several options for passing data from a component buffer to an Application Engine program: you can use the CallAppEngine PeopleCode function or you can define global variables.

Passing Parameters Through the CallAppEngine Function

For individual page fields and simple PeopleCode variables such as numbers and strings, you can use the CallAppEngine PeopleCode function to pass values as parameters.

To use the CallAppEngine function:

  1. Declare a record object in PeopleCode.

    For example, Local Record &MyRecord;.

  2. Assign record objects to any state record that you want to pass to the Application Engine program.

    Record objects are parameters to the CallAppEngine function.

  3. Set the appropriate values on that state record.

  4. Include the record object in the function call.

After these values are set in the state record, all the actions in a particular program, not just the PeopleCode actions, can use the values.

Defining Global Variables

You can define global variables or objects in PeopleCode before you call an Application Engine program. Application Engine PeopleCode actions are able to access only the variables you define; however, the PeopleCode could set a state record field equal to a number or string variable for use by other Application Engine actions.

Also, an Application Engine PeopleCode program can read or update a scroll area or a grid using a global rowset object. When accessing a scroll area or a grid from Application Engine PeopleCode, the same rules apply and the same illegal operations are possible that you see with accessing PeopleCode not in an Application Engine program.

The parameters submitted in a CallAppEngine are by value. These parameters seed the specified Application Engine state record field with a corresponding value. If that value changes within Application Engine by updating the state record field, then the component data will not be affected. The only way to update component buffers or external PeopleCode variables from Application Engine is to use global PeopleCode variables and objects.

See Also

CallAppEngine

Click to jump to top of pageClick to jump to parent topicAccessing State Records with PeopleCode

Running PeopleCode from Application Engine steps enables you to complete some simple operations without having to use SQL. For example, to assign a literal value to an Application Engine state record field using SQL, you may have issued a statement similar to this one:

%SELECT(MY_AET.MY_COLUMN) SELECT 'BUSINESS_UNIT' FROM PS_INSTALLATION

You can use a PeopleCode assignment instead:

MY_AET.MY_COLUMN = "BUSINESS_UNIT";

Similarly, you can use a PeopleCode If statement instead of a Do When action to check the value of a state record field.

When accessing state records with PeopleCode, keep in mind that:

Note. When you launch an Application Engine program from PeopleSoft Process Scheduler, you can generate a process warning status after the program completes by including and modifying the AE_APPSTATUS field in a state record. You can generate the warning status by setting AE_APPSTATUS to a value of 1.

Click to jump to top of pageClick to jump to parent topicUsing If/Then Logic

From PeopleCode, you can trigger an error status, or false return, by using the Exit function. Use the On Return value in the PeopleCode action properties to specify how your Application Engine program behaves according to the return of your PeopleCode program. This example shows the On Return property:

By default, the program terminates, similar to what happens when a SQL error occurs. By changing the On Return value to Skip Step, however, you can control the flow of your Application Engine program.

You can use Exit to add an If condition to a step or a section break. For example:

If StateRec.Field1 = ‘N’ Exit(1); Else /* Do processing */ End-if;

You must specify a non-zero return value to trigger an On Return action. The concepts of “return 1” and “return True” are equivalent; therefore, if the return value is non-zero or True, then Application Engine performs what you specify for On Return, as in Abort or Skip Step. However, if the program returns zero or False, Application Engine ignores the selected On Return value.

Click to jump to top of pageClick to jump to parent topicUsing PeopleCode in Loops

You can insert PeopleCode inside of a Do loop, but be careful when using PeopleCode inside of high-volume Do loops (While, Select, Until). Minimize the number of distinct programs inside the loop. You should avoid having PeopleCode perform the actual work of the program and instead use it primarily to control the flow (If, Then logic), build dynamic SQL, or interact with external systems.

Using bind variables instead of literals to pass values to SQL statements is essential in PeopleCode loops or if the PeopleCode is called in a loop. If the PeopleCode loops, Application Engine probably will use a dedicated cursor, which saves the overhead of recompiling the SQL for all iterations. If the PeopleCode is called from within a loop, Application Engine does not reduce the number of compiles, but it avoids flooding the SQL cache (for those database servers that support SQL cache) when it uses bind variables. Do not use bind variables for values in a Select list or for SQL identifiers, such as table and column names, as some databases do not support them.

Note. Null bind values of type DateTime, Date, or Time are always resolved into literals.

On database platforms for which this feature is implemented, setting BulkMode to True often results in significant performance gains when inserting rows into a table within a loop.

In general, avoid PeopleCode calls within a loop. If you can call the PeopleCode outside of the loop, use that approach to increase overall performance.

Click to jump to top of pageClick to jump to parent topicUsing the AESection Class

The AESection PeopleCode class enables you to change the properties of an Application Engine program section dynamically, without having to modify any of the Application Engine tables directly. This capability enables you to develop rule-based applications that conform dynamically to variables that a user submits through a page, such as the Application Engine Request page.

The AESection class provides the following flexibility:

When using an AESection object:

Note. Do not call an AESection object from an Application Engine PeopleCode action.

See Also

AESection Class

Click to jump to top of pageClick to jump to parent topicMaking Synchronous Online Calls to Application Engine Programs

To make synchronous online calls to an Application Engine program, use the PeopleCode function CallAppEngine.

Note. If you make a synchronous call, users cannot perform another PeopleSoft task until the Application Engine program completes. Consider the size and performance of the Application Engine program called by CallAppEngine. You should ensure that the program will run to successful completion consistently within an acceptable amount of time.

If an Application Engine program called by CallAppEngine terminates abnormally, the user receives an error, similar to other save time errors, that forces the user to cancel the operation. The CallAppEngine function returns a value based on the result of the Application Engine call. If the program was successful, it returns a zero; if the program was unsuccessful, it returns a value other than zero.

See Also

CallAppEngine

Click to jump to top of pageClick to jump to parent topicUsing the File Class

The file layout class enables you to perform file input and output operations with Application Engine using PeopleCode. A file object enables you to open a file (for reading or writing), read data from a file, or write data to it. Using the combination of the file class and Application Engine provides an effective method to integrate (or exchange) the data stored in a legacy system with your PeopleSoft system. The file class facilitates the creation of a flat file that both your legacy system and Application Engine programs support.

An Application Engine program running on the application server uses a file object to read the file sent from the legacy system and to translate it so that the file can update affected PeopleSoft application tables. For the PeopleSoft system and the legacy system to communicate, you first must construct a file object that both systems can use to insert and read data.

Attain rowset and record access for a file using a file layout definition. You create the file layout definition in Application Designer, and it acts as a template for the file that both systems read from and write to. This file layout definition simplifies reading, writing, and manipulating complex transaction data with PeopleCode.

Generally, use the file class and Application Engine combination when you cannot implement the PeopleSoft Integration Broker solution.

See Also

File Class

Click to jump to top of pageClick to jump to parent topicCalling COBOL Modules

Using the PeopleCode RemoteCall function, you can call COBOL modules from a PeopleCode action. This option supports existing Application Engine programs that call COBOL modules. You also can use it to upgrade Application Engine programs from previous releases.

PTPECOBL Program

The PTPECOBL interface program is a PeopleSoft executable that enables you to invoke your called COBOL module and pass it required values. You code the RemoteCall function to invoke PTPECOBL, which in turn calls the specified COBOL module.

If you use PTPECOBL, you do not have to write your own executable to process this task. However, PTPECOBL does not perform any SQL processing other than retrieving a list of state record values. Consequently, if your current logic requires previous SQL processing, you may want to write your own executable file to call your COBOL module. In most situations, PTPECOBL saves you from having to write a custom executable file to handle each call to a generated dynamically loadable code (.GNT) file.

PTPECOBL performs the following tasks:

  1. Initializes the specified state record in memory.

  2. Invokes the COBOL module specified in your PeopleCode.

  3. Submits required parameters to the called COBOL module.

  4. Updates the state record as necessary, issues a commit, and then disconnects from the database after your program completes.

Note. While your COBOL program runs, it can access and return values to the state record.

Shared Values in Application Engine and COBOL

The following options are available for sharing values between the Application Engine program and a called COBOL program:

Syntax and Parameters

This example shows a sample RemoteCall function issued from an Application Engine PeopleCode action to a COBOL module:

RemoteCall ("PSRCCBL",? "PSCOBOLPROG", "PTPECOBL",? "AECOBOLPROG", "MY_GNT",? "STATERECORD", "MY_AET",? "PRCSINST", MY_AET.PROCESS_INSTANCE,? "RETCODE", &RC,? "ERRMSG", &ERR_MSG,? "FIELD1", MY_AET.FIELD1,? "FIELD2", MY_AET.FIELD2);

This table describes each parameter in the RemoteCall function:

Parameters

Description

PSRCCBL

The Remote Call dispatcher, which runs the specified COBOL program using the connect information of the current operator.

PSCOBOLPROG

Specify the name of the COBOL program to run, which in this case is PTPECOBL.

This parameter makes the remote call from Application Engine distinct from a normal remote call. When you enter this parameter, in effect you enable the following parameters, some of which are required.

AECOBOLPROG

Specify the name of the COBOL module you are calling; for example, MY_GNT.

STATERECORD

Specify the appropriate state record that your Application Engine program will share with your COBOL module; for example, MY_AET. PTPECOBL then reserves space in memory for all of the fields in the state record, regardless of whether they will ultimately store values for processing.

PRCSINST

Specify the state record and Process Instance field; for example, MY_AET.PROCESS_INSTANCE. This setting retrieves the current process instance value that appears on the state record and submits it to your COBOL module using PTPECOBL.

RETCODE and ERRMSG

(Optional) Include RETCODE if you need to return information about any potential problems that the COBOL processing encountered, or use it if your Application Engine program must know whether it completed successfully.

Fieldnames and Values

Specify any fields in the state record that contain initial values for your COBOL module. The quoted field names you specify must exist in the specified state record. The corresponding value can be a PeopleCode variable, a record.field reference, or a hard-coded value.

Commit and RemoteCall

When using RemoteCall and an Application Engine program:

Note. By default, RemoteCall does not generate any log files after the program completes. To generate and retain the .out and .err log files, you must set the RCCBL Redirect parameter in the PeopleSoft Process Scheduler configuration file to a value of 1.

See SetNextPanel.

See Also

Editing the PeopleSoft Process Scheduler Configuration File

Click to jump to top of pageClick to jump to parent topicCalling PeopleTools APIs

You can call all of the PeopleTools APIs from an Application Engine program. When using APIs, remember that:

Click to jump to top of pageClick to jump to parent topicUsing the CommitWork Function

This function commits pending changes (inserts, updates, and deletes) to the database. When using CommitWork, remember that:

See Also

CommitWork

Click to jump to top of pageClick to jump to parent topicCalling WINWORD Mail Merge

If the Process Scheduler is booted using a shared drive on another machine and you intend to call a WINWORD mail merge process from Application Engine, then you must do one of the following to ensure successful completion:

  1. Configure the Process Scheduler to run Application Engine programs using psae instead of psaesrv.

  2. Ensure the generated document is saved locally, not on a shared network drive.

Click to jump to top of pageClick to jump to parent topicUsing PeopleCode Examples

The following topics provide examples of common ways that you can use PeopleCode within Application Engine programs.

Do When Actions

Instead of a Do When action that checks a %BIND value, you can use PeopleCode to perform the equivalent operation. For example, suppose the following SQL exists in your program:

%SELECT(EXISTS) SELECT 'Y' FROM PS_INSTALLATION WHERE %BIND(TYPE) = 'X'),

Using PeopleCode, you could insert this code:

If TYPE = 'X' Then Exit(0); Else Exit(1); End-if;

If you set the On Return parameter on the PeopleCode action properties to Skip Step, this code behaves the same as the Do When action. The advantage of using PeopleCode is that no trip to the database occurs.

Dynamic SQL

If you have a Select statement that populates a text field with dynamic SQL, such as the following:

%SELECT(AE_WHERE1) SELECT 'AND ACCOUNTING_DT <= %Bind(ASOF_DATE)'

You can use this PeopleCode:

AE_WHERE1 = "AND ACCOUNTING_DT <= %Bind(ASOF_DATE)";

Sequence Numbering

If you typically use Select statements to increment a sequence number inside of a Do Select, While, or Until loop, you can use the following PeopleCode instead:

SEQ_NBR = SEQ_NBR + 1;

Using PeopleCode rather than SQL can affect performance significantly. Because the sequencing task occurs repeatedly inside a loop, the cost of using a SQL statement to increment the counter increases with the volume of transactions your program processes. When you are modifying a program to take advantage of PeopleCode, the areas of logic you should consider are those that start with steps that run inside a loop.

Note. You can also use the meta-SQL constructs %Next and %Previous when performing sequence numbering. These constructs may improve performance in both PeopleCode and SQL calls.

Rowsets

You can use rowsets in Application Engine PeopleCode; however, using rowsets means you will be using PeopleCode to handle more complicated processing, which degrades performance.

Math Functions

Use the math functions that your database offers whenever possible.

Internally, PeopleCode assigns types to numeric values. Calculations for the Decimal type are processed in arrays to ensure decimal point uniformity across hardware and operating system environments. This processing is much slower than calculations for type Integer, which are processed at the hardware level.

When PeopleCode converts strings to numeric values, it does so using the internal Decimal type. For performance reasons, avoid calculations using these values.

A third type of numeric value is the Float type. It is not used as frequently for the following reasons:

Use the Float type to produce a float result only if all operands are also of the Float type. Float operations occur at the hardware level.

PeopleCode does not offer optimum performance when processing non-Integer, non-Float math calculations. To perform calculations with these numeric types, consider allowing the database to perform the calculations in COBOL.

PeopleCode supports a range of mathematical functions and numeric types. In general, if a complex calculation is run repetitively in an Application Engine program, you should carefully analyze whether to perform the calculation in a PeopleCode action or use the relational database management system (RDBMS) functions through a SQL action. Using SQL may require PeopleSoft meta-SQL to handle platform differences, but it may be the most efficient way to update field values. If SQL is not appropriate, consider numeric typing in PeopleCode, as it affects the speed and accuracy of the calculation.

SQL Class

Instead of using the SQL class within PeopleCode, have Application Engine issue the SQL and use a Do Select action that loops around sections containing PeopleCode actions.

Coding all of the logic within a single PeopleCode program might appear to be easier, but splitting the logic into smaller pieces is preferable because you will have better performance and will get more detailed commit control. Within a PeopleCode program, you can commit in certain cases using the CommitWork function. You can always issue a commit between Application Engine steps.

See CommitWork.

See Understanding SQL Objects and Application Engine Programs.

Arrays

Instead of using arrays in Application Engine PeopleCode, explore the use of temporary tables for storing pertinent or affected data. Arrays offer the following advantages:

You can use the Statement Timings and PeopleCode Detail Timings trace options to generate an Application Engine timings report to determine whether your program is spending significant time processing arrays.

Click to jump to parent topicIncluding Dynamic SQL

Typically, developers include dynamic constructs in Application Engine programs to change SQL based on various runtime factors or on user-defined entries on a page. You can include dynamic SQL in Application Engine programs in a variety of ways. For example, you can:

The AESection class is designed primarily for online section building; therefore, it will not be the most frequently used solution.

Use the SQL class to store SQL in a SQL definition that you can also access in Application Designer. Then, if you have a few SQL statements to run, generate the SQL IDs based on some methodology, such as a timestamp, and then store these in a table.

When the program runs, your SQL could query this table based on process and extract the appropriate SQL IDs to be run with a SQL action in a Do Select loop.

%SQL(%BIND(MY_SQLID, NOQUOTES))

For a dynamic Do action, the AE_APPLID and the AE_SECTION fields must appear in the default state record.

Click to jump to parent topicUsing Application Engine Meta-SQL

This section describes the meta-SQL constructs, functions, and meta-variables you can use in Application Engine.

Note. The SQL Editor does not validate all of the meta-SQL constructs, such as %Bind and %Select. Messages might appear stating that these constructs are invalid.

Click to jump to top of pageClick to jump to parent topic%Abs

Description

Because the %Abs function can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

See Also

%Abs

Click to jump to top of pageClick to jump to parent topic%AeProgram

Description

Use the %AeProgram meta-variable to specify a quoted string containing the currently running Application Engine program name.

Click to jump to top of pageClick to jump to parent topic%AeSection

Description

Use the %AeSection meta-variable to specify a quoted string containing the currently running Application Engine section name.

Click to jump to top of pageClick to jump to parent topic%AeStep

Description

Use the %AeStep meta-variable to specify a quoted string containing the currently running Application Engine Step name.

Click to jump to top of pageClick to jump to parent topic%AsOfDate

Description

Use the %AsOfDate meta-variable to specify a quoted string containing the as of date used for the current process.

Click to jump to top of pageClick to jump to parent topic%AsOfDateOvr

Description

Use the %AsOfDateOvr meta-variable only as a parameter of the %ExecuteEdits function to override the default use of the system date with the value of a field on a joined record.

See Also

%Table.

Click to jump to top of pageClick to jump to parent topic%BINARYSORT

Description

Because the %BINARYSORT construct can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

See Also

%BINARYSORT

Click to jump to top of pageClick to jump to parent topic%Bind

Syntax

%Bind([recordname.]fieldname [, NOQUOTES][, NOWRAP][, STATIC])

Description

Use the %Bind construct to retrieve a field value from a state record. You can use %Bind anywhere in a SQL statement. When run, %Bind returns the value of the state record field identified within its parentheses.

Notes About %Bind

Typically, when you use %Bind to provide a value for a field or a Where condition, the type of field in the state record that you reference with %Bind must match the field type of the corresponding database field used in the SQL statement.

On most platforms, you cannot use a literal to populate a Long Varchar field. You should use the %Bind(recordname.fieldname) construct.

In the case of an external call to a section in another program, if the called program has its own default state record defined, then Application Engine uses that default state record to resolve the %Bind(fieldname). Otherwise, the called program inherits the default state record of the calling program.

All fields referenced by a %Select construct must be defined in the associated state record.

You must use the Date, Time, and DateTime output wrappers in the Select list that populates the state record fields to ensure compatibility across all supported database platforms.

For example:

Bind Variables and Date Wraps

The behavior of bind variables within Application Engine PeopleCode and normal PeopleCode is the same.

If you compare Application Engine SQL to PeopleCode (of any type), then the system processes bind variables differently.

If you use the following approach:

AND TL_EMPL_DATA1.EFFDT <= %P(1))

Then in PeopleCode you issue

%SQL(MY_SQL, %DateIn(:1))

which assumes that you referenced the literal as a bind variable.

Or in Application Engine SQL, you issue

%SQL(MY_SQL, %Bind(date_field)) %SQL(MY_SQL, %Bind(date_field, NOWRAP))

Parameters

Recordname

The name of a state record. If you do not specify a particular state record, Application Engine uses the default state record to resolve the %Bind (fieldname).

Fieldname

The field defined in the state record.

NOQUOTES

If the field specified is a character field, its value is automatically enclosed in quotes unless you use the NOQUOTES parameter. Use NOQUOTES to include a dynamic table and field name reference, even an entire SQL statement or clause, in an Application Engine SQL action.

NOWRAP

If the field is of type Date, Time, or DateTime, the system automatically wraps its value in %DateIn or %DateOut, unless you use the NOWRAP parameter. Therefore, if the state record field is populated correctly, you do not need to be concerned with the inbound references, although you can suppress the inbound wrapping with the NOWRAP modifier inside the %Bind. Furthermore, Application Engine skips the inbound wrapper if the %Bind (date) is in the select field list of another %Select statement. This is because the bind value is already in the outbound format, and the system selects it into another state record field in memory. In this circumstance there is no need for either an outbound wrapper or an inbound wrapper. For example,

First SQL action:

%Select(date_end) SELECT %DateOut(date_end ) FROM PS_GREG

Second SQL action:

INSERT INTO ps_greg VALUES(%Bind(date_end))

STATIC

The STATIC parameter enables you to include a hard-coded value in a reused statement. For %Bind instances that contain dynamic SQL, this parameter must be used in conjunction with the NOQUOTES parameter for proper execution of a reused statement.

Example

UPDATE PS_REQ_HDR SET IN_PROCESS_FLG = %Bind(MY_AET.IN_PROCESS_FLG), PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE) WHERE IN_PROCESS_FLG = ‘N’ AND BUSINESS_UNIT || REQ_ID IN (SELECT BUSINESS_UNIT ||REQ_ID FROM PS_PO_REQRCON_WK1 WHERE PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE))

In the previous example, %Bind (PROCESS_INSTANCE) assigns the value of the field PROCESS_INSTANCE in the default state record to the PROCESS_INSTANCE field in table PS_REQ_HDR.

The %Bind construct is also used in a Where clause to identify rows in the table PS_PO_REQRCON_WK1, in which the value of PROCESS_INSTANCE equals the value of PROCESS_INSTANCE in the default state record.

Click to jump to top of pageClick to jump to parent topic%Cast

Description

Because the %Cast function can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

See Also

%Cast

Click to jump to top of pageClick to jump to parent topic%ClearCursor

Syntax

%ClearCursor({program, section, step, action | ALL})

Description

Use the %ClearCursor function to recompile a reused statement and reset any STATIC %Bind variables.

When you use the %ClearCursor function, remember that:

Parameters

program

Specify the name of the Application Engine program containing the reused statement you want to recompile.

section

Specify the name of the section containing the reused statement you want to recompile.

step

Specify the name of the step containing the reused statement you want to recompile.

action

Specify one of the following values:

  • D: Do Select.

  • H: Do When.

  • N: Do Until.

  • W: Do While.

  • S: SQL.

ALL

Clear all cursors in the current Application Engine program.

Click to jump to top of pageClick to jump to parent topic%COALESCE

Description

Because the %COALESCE function can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

See Also

%COALESCE

Click to jump to top of pageClick to jump to parent topic%Comma

Description

Use the %Comma meta-variable to specify a comma. This meta-variable is useful when you must use a comma but commas are not allowed because of parsing rules. For example, you might use this meta-variable if you want to pass a comma as a parameter to the %SQL meta-SQL function.

See Also

%SQL

Click to jump to top of pageClick to jump to parent topic%Concat

Description

Because the %Concat meta-variable can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

See Also

%Concat

Click to jump to top of pageClick to jump to parent topic%CurrentDateIn

Description

Because the %CurrentDateIn meta-variable can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

See Also

%CurrentDateIn

Click to jump to top of pageClick to jump to parent topic%CurrentDateOut

Description

Because the %CurrentDateOut meta-variable can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

See Also

%CurrentDateOut

Click to jump to top of pageClick to jump to parent topic%CurrentDateTimeIn

Description

Because the %CurrentDateTimeIn meta-variable can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

See Also

%CurrentDateTimeIn

Click to jump to top of pageClick to jump to parent topic%CurrentDateTimeOut

Description

Because the %CurrentDateTimeOut meta-variable can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

See Also

%CurrentDateTimeOut

Click to jump to top of pageClick to jump to parent topic%CurrentTimeIn

Description

Because the %CurrentTimeIn meta-variable can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

See Also

%CurrentTimeIn

Click to jump to top of pageClick to jump to parent topic%CurrentTimeOut

Description

Because the %CurrentTimeOut meta-variable can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

See Also

%CurrentTimeOut

Click to jump to top of pageClick to jump to parent topic%DateAdd

Description

Because the %DateAdd function can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

See Also

%DateAdd

Click to jump to top of pageClick to jump to parent topic%DateDiff

Description

Because the %DateDiff function can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

See Also

%DateDiff

Click to jump to top of pageClick to jump to parent topic%DateIn

Description

Because the %DateIn construct can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

See Also

%DateIn

Click to jump to top of pageClick to jump to parent topic%DateNull

Description

Because the %DateNull meta-variable can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

See Also

%DateNull

Click to jump to top of pageClick to jump to parent topic%DateOut

Description

Because the %DateOut function can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

See Also

%DateOut

Click to jump to top of pageClick to jump to parent topic%DatePart

Description

Because the %DatePart function can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

See Also

%DatePart

Click to jump to top of pageClick to jump to parent topic%DateTimeDiff

Description

Because the %DateTimeDiff function can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

See Also

%DateTimeDiff

Click to jump to top of pageClick to jump to parent topic%DateTimeIn

Description

Because the %DateTimeIn construct can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

See Also

%DateTimeIn

Click to jump to top of pageClick to jump to parent topic%DateTimeNull

Description

Because the %DateTimeNull meta-variable can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

See Also

%DateTimeNull

Click to jump to top of pageClick to jump to parent topic%DateTimeOut

Description

Because the %DateTimeOut function can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

See Also

%DateTimeOut

Click to jump to top of pageClick to jump to parent topic%DecDiv

Description

Because the %DecDiv function can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

See Also

%DecDiv

Click to jump to top of pageClick to jump to parent topic%DecMult

Description

Because the %DecMult function can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

See Also

%DecMult

Click to jump to top of pageClick to jump to parent topic%Delete

Description

Because the %Delete shortcut can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

Note. This meta-SQL can only be used in PeopleCode programs, not in Application Engine SQL actions.

See Also

%Delete

Click to jump to top of pageClick to jump to parent topic%DTTM

Description

Because the %DTTM function can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

See Also

%DTTM

Click to jump to top of pageClick to jump to parent topic%EffDtCheck

Description

Because the %EffDtCheck construct can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

See Also

%EffDtCheck

Click to jump to top of pageClick to jump to parent topic%Execute

Syntax

%Execute([/]) command1{; | /} command2{; | /}... commandN{; | /}

Description

Use the %Execute function to execute database-specific commands from within your Application Engine program. Also, the %Execute function enables you to include multiple statements in a single Application Engine action without encountering database-specific differences. For instance, in some instances you could code a single Application Engine action to contain multiple SQL statements, and they might run successfully on one database platform. However, if you attempt to run the same code against a different database platform, you might encounter errors or skipped SQL.

By default, Application Engine expects a semicolon to be used to delimit multiple commands within an %Execute function statement. You can instruct Application Engine to use a forward slash (/) delimiter instead by placing a forward slash inside the function parentheses.

Note. When you use the %Execute function, it must be located at the beginning of the statement and must be the only function or command contained in the statement. The action type must be SQL.

Example

The following code enables you to use an Oracle PL/SQL block in an %Execute statement:

%Execute(/) DECLARE counter INTEGER; BEGIN FOR counter := 1 TO 10 UPDATE pslock SET version = version + 1; END FOR; END; /

Click to jump to top of pageClick to jump to parent topic%ExecuteEdits

Syntax

%ExecuteEdits(type, recordname [alias][, field1, field2, ...])

Description

Use the %ExecuteEdits function to apply data dictionary edits in batch. The %ExecuteEdits function is Application Engine-only meta-SQL. You cannot use it in COBOL, SQR, or PeopleCode, not even in Application Engine PeopleCode.

Notes About %ExecuteEdits

Note the following points about the %ExecuteEdits function:

Parameters

type

Specify any combination of the following (added together):

  • %Edit_Required

  • %Edit_YesNo

  • %Edit_DateRange

  • %Edit_PromptTable

  • %Edit_TranslateTable

recordname

Specify the record used to obtain the data dictionary edits.

field1, field2, ...

Specify a subset of the fields of the record to which edits apply.

Example

Suppose you want to insert rows with missing or invalid values in three specific fields, selecting data from a temporary table but using edits defined on the original application table. Notice the use of an alias, or correlation name, inside the meta-SQL:

INSERT INTO PS_JRNL_LINE_ERROR (...) SELECT ... FROM PS_JRNL_LINE_TMP A WHERE A.PROCESS_INSTANCE = %BIND(PROCESS_INSTANCE) AND %EXECUTEEDITS(%Edit_Required + %Edit_PromptTable,? JRNL_LINE A, BUSINESS_UNIT, JOURNAL_ID, ACCOUNTING_DT)

To update rows that have some kind of edit error in a temporary table, you can use custom edits defined in the temporary table record:

UPDATE PS_PENDITEM_TAO SELECT ERROR_FLAG = 'Y' WHERE PROCESS_INSTANCE = %BIND(PROCESS_INSTANCE) AND %EXECUTEEDITS(%Edit_Required + %Edit_YesNo + %Edit_DateRange +? %Edit_PromptTable + %Edit_TranslateTable, PENDITEM_TAO)

Click to jump to top of pageClick to jump to parent topic%FirstRows

Description

Because the %FirstRows meta-variable can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

See Also

%FirstRows

Click to jump to top of pageClick to jump to parent topic%GetProgText

Syntax

%GetProgText(&Prog, &Section, &Market, &Platform, &Effdt, &Step, &Event)

Description

The %GetProgText function returns a string with the text of a PeopleCode program uniquely identified by the parameters.

Parameters

&Prog

A string with the name of an Application Engine program.

&Section

A string with the name of an Application Engine program section.

&Market

A string specifying the market for an Application Engine program section.

&Platform

A string specifying the platform for an Application Engine program section.

&Effdt

A string specifying the effective date for an Application Engine program section.

&Step

A string specifying a step in an Application Engine program section.

&Event

A string specifying the PeopleCode event.

Returns

A string containing the text of a PeopleCode program.

Example

&PeopleCodeText = GetProgText("DYNROLE_PUBL", "MAIN", "GBL", "default", "1900-01-01", "Step03", "OnExecute");

See Also

Using the SQL Editor.

Click to jump to top of pageClick to jump to parent topic%Insert

Description

Because the %Insert shortcut can be used in more than just Application Engine programs, it is documented in thePeopleTools 8.52: PeopleCode Language Reference PeopleBook.

Note. This meta-SQL can only be used in PeopleCode programs, not in Application Engine SQL actions.

See Also

%Insert

Click to jump to top of pageClick to jump to parent topic%InsertSelect

Description

Because the %InsertSelect construct can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

See Also

%InsertSelect

Click to jump to top of pageClick to jump to parent topic%InsertSelectWithLongs

Description

Because the%InsertSelectWithLongs construct can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

See Also

%InsertSelectWithLongs

Click to jump to top of pageClick to jump to parent topic%InsertValues

Description

Because the %InsertValues construct can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

Note. This meta-SQL can only be used in PeopleCode programs, not in Application Engine SQL actions.

See Also

%InsertValues

Click to jump to top of pageClick to jump to parent topic%JobInstance

Description

Use the %JobInstance meta-variable to specify the numeric (unquoted) PeopleSoft Process Scheduler job instance.

Click to jump to top of pageClick to jump to parent topic%Join

Description

Because the %Join construct can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

See Also

%Join

Click to jump to top of pageClick to jump to parent topic%KeyEqual

Description

Because the %KeyEqual construct can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

Note. This meta-SQL can only be used in PeopleCode programs, not in Application Engine SQL actions.

See Also

%KeyEqual

Click to jump to top of pageClick to jump to parent topic%KeyEqualNoEffDt

Description

Because the %KeyEqualNoEffDt construct can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

Note. This meta-SQL can only be used in PeopleCode programs, not in Application Engine SQL actions.

See Also

%KeyEqualNoEffDt

Click to jump to top of pageClick to jump to parent topic%LeftParen

Description

Use the %LeftParen meta-variable to specify a left parenthesis. Usage is similar to %Comma.

See Also

%Comma

%SQL

Click to jump to top of pageClick to jump to parent topic%Like

Description

Because the %Like construct can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

See Also

%Like

Click to jump to top of pageClick to jump to parent topic%LikeExact

Description

Because the %LikeExact construct can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

See Also

%LikeExact

Click to jump to top of pageClick to jump to parent topic%List

Syntax

%List({FIELD_LIST | FIELD_LIST_NOLONGS | KEY_FIELDS | ORDER_BY}, recordname [ correlation_id])

Description

The %List construct expands into a list of field names delimited by commas. The fields included in the expanded list depend on the parameters.

Note. This meta-SQL is not implemented for COBOL, dynamic view SQL, or PeopleCode.

Considerations for Using %List

When using %List in an Insert/Select or Insert/Values or %Select statement, you must have matching pairs of %List (or %ListBind) variables in the target and source field lists. Use the same list type argument and record name to ensure consistency.

Parameters

FIELD_LIST

Use all field names in the given record. You can select only one option from FIELD_LIST, ORDER_BY, FIELD_LIST_NOLONGS, or KEY_FIELDS.

KEY_FIELDS

Use all key fields in the given record. You can select only one option from FIELD_LIST, FIELD_LIST_NOLONGS, KEY_FIELDS, or ORDER_BY.

ORDER_BY

Use all the key fields of recordname, adding the DESC field for descending key columns. This parameter is often used when the list being generated is for an Order By clause. You can select only one option from FIELD_LIST, KEY_FIELDS, ORDER_BY, or FIELD_LIST_NOLONGS.

FIELD_LIST_NOLONGS

Use all field names in the given record, except any long columns (long text or image fields.) You can select only one option from FIELD_LIST, ORDER_BY, KEY_FIELDS, or FIELD_LIST_NOLONGS.

recordname

Identify either a record or a subrecord that the field names are drawn from. This can be a bind variable, a record object, or a record name in the form recname. You cannot specify RECORD.recname, a record name in quotation marks, or a table name.

correlation_id

Identify the single-letter correlation ID to relate the record specified by recordname and its fields.

Example

The following is a good example of using %List. Both the Insert and Select statements use the same %List variable:

INSERT INTO PS_PO_DISTRIB_STG ( %Sql(POCOMMONDISTSTGFLDLSTU) , %List(FIELD_LIST, CF16_AN_SBR) , MERCHANDISE_AMT , MERCH_AMT_BSE , QTY_DEMAND , QTY_PO , QTY_PO_STD , QTY_REQ) SELECT %Sql(POCOMMONDISTSTGFLDLSTU) , %List(FIELD_LIST, CF16_AN_SBR) , MERCHANDISE_AMT , MERCH_AMT_BSE , QTY_DEMAND , QTY_PO , QTY_PO_STD , QTY_REQ FROM PS_PO_DIST_STG_WRK WRK WHERE WRK.PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE)

The following example shows a poor example of how to use %List. The Insert and Select field lists both use %List, but the Select field list is only partly dynamic; the rest is hard-coded.

INSERT INTO PS_EN_TRN_CMP_TMP (%List(FIELD_LIST, EN_TRN_CMP_TMP)) SELECT B.EIP_CTL_ID , %List(SELECT_LIST, EN_BOM_COMPS A) , E.COPY_DIRECTION , E.BUSINESS_UNIT_TO , E.BOM_TRANSFER_STAT , 'N' , B.MASS_MAINT_CODE , 0 FROM PS_EN_BOM_COMPS A , PS_EN_ASSY_TRN_TMP B , PS_EN_TRNS_TMP E WHERE ...

The following example shows the previous poor example rewritten in a better way:

INSERT INTO PS_EN_TRN_CMP_TMP (EIP_CTL_ID, , %List(FIELD_LIST, EN_BOM_COMPS) , COPY_DIRECTION , BUSINESS_UNIT_TO , BOM_TRANSFER_STAT , EN_MMC_UPDATE_FLG , MASS_MAINT_CODE , EN_MMC_SEQ_FLG01 , ... , EN_MMC_SEQ_FLG20) SELECT B.EIP_CTL_ID , %List(FIELD_LIST, EN_BOM_COMPS A) , E.COPY_DIRECTION , E.BUSINESS_UNIT_TO , E.BOM_TRANSFER_STAT , 'N' , B.MASS_MAINT_CODE , 0 , ... , 0 FROM PS_EN_BOM_COMPS A , PS_EN_ASSY_TRN_TMP B , PS_EN_TRNS_TMP E WHERE ...

The following code segment is another poor example. Only the field list of the Insert statement is dynamically generated, and the Select statement is statically coded. If the table STL_NET_TBL is reordered, the Insert statement will be incorrect.

INSERT INTO PS_STL_NET_TBL (%List(FIELD_LIST, STL_NET_TBL ) ) SELECT :1 , :2 , :3 , :4 , :5 , :6 , :7 ,:8 FROM PS_INSTALLATION

The following code shows the previous poor example rewritten in a better way:

INSERT INTO PS_STL_NET_TBL (%List(FIELD_LIST, STL_NET_TBL)) VALUES (%List(BIND_LIST, STL_NET_TBL ​MY_AET))

Click to jump to top of pageClick to jump to parent topic%ListBind

Syntax

%ListBind({FIELD_LIST | FIELD_LIST_NOLONGS | KEY_FIELDS}, recordname [ State_record_alias])

Description

The %ListBind meta-SQL construct expands a field list as bind references for use in an Insert/Value statement.

Note. This meta-SQL is not implemented for COBOL, dynamic view SQL, or PeopleCode.

Considerations for Using %ListBind

When using %ListBind in an insert/select or insert/values or %Select statement, you must have matching pairs of %List or %ListBind in the target and source field lists, using the same list type argument and record name to ensure consistency.

Parameters

FIELD_LIST

Use all field names in a record. You can select only one option from FIELD_LIST, FIELD_LIST_NOLONGS, or KEY_FIELDS.

FIELD_LIST_NOLONGS

Use all field names in a record, except any long columns (long text or image fields). You can select only one option from FIELD_LIST, FIELD_LIST_NOLONGS, or KEY_FIELDS.

KEY_FIELDS

Use all key field names in a record. You can select only one option from FIELD_LIST, FIELD_LIST_NOLONGS, or KEY_FIELDS.

recordname

Identify either a record or a subrecord that the field names are drawn from. This can be a bind variable, a record object, or a record name in the form recname. You cannot specify RECORD.recname, a record name in quotation marks, or a table name.

State_record_alias

Specify the Application Engine state record buffer that contains the values (this could be different than the record used to derive the field list). If missing, the default state record is assumed.

Example

INSERT INTO PS_TARGET (FIELD1, FIELD2, %List(FIELD_LIST, CF_SUBREC), FIELDN)⇒ VALUES (%Bind(MY_AET.FIELD1), %Bind(MY_AET.FIELD2), %ListBind(FIELD_LIST, CF_⇒ SUBREC MY_AET), %Bind(MY_AET.FIELDN))

Click to jump to top of pageClick to jump to parent topic%ListEqual

Syntax

%ListEqual({ALL | KEY }, Recordname [alias], RecordBuffer [, Separator])

Description

The %ListEqual construct maps each field, possibly to an alias with a %Bind value, with a separator added before each equality. Each field is mapped as follows:

alias.X = %Bind(recbuffer.X)

This construct can be used in the Set clause of an Update statement or in a Where clause.

Note. This meta-SQL is not implemented for COBOL, dynamic view SQL, or PeopleCode.

Parameters

ALL | KEY

Specify if you want all fields or just key fields.

recordname

Identify either a record or a subrecord that the field names are drawn from. This can be a bind variable, a record object, or a record name in the form recname. You cannot specify RECORD.recname, a record name in quotation marks, or a table name.

alias

(Optional) Specify an alias to precede each field name.

RecordBuffer

Specify the record buffer for the bind variables (this could be different than the record used to derive the field list).

Separator

If you want to specify a logical separator, specify either AND or OR with this parameter. If you do not specify a separator, no logical separator is used; the value of a comma is used instead.

Example

UPDATE PS_TEMP SET %ListEqual(ALL, CF_SUBREC, MY_AET) WHERE %ListEqual(KEYS, TEMP, MY_AET, AND)

Click to jump to top of pageClick to jump to parent topic%Mod

Description

Because the %Mod function can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

See Also

%Mod

Click to jump to top of pageClick to jump to parent topic%Next and %Previous

Description

Use the %Next and %Previous functions to return the value of the next or previous field in a numbered sequence. These functions are valid in any Application Engine SQL action and should be used when performing sequence-numbering processing. Typically, you use them instead of a %Bind construct. These functions use the current value of the number field as a bind variable and then increment (%Next) or decrement (%Previous) the value after the statement runs successfully. A number field indicates the numeric field of the state record that you initially set to a particular value (as in 1 to start).

If the statement is a Select and no rows are returned, the field value is not changed. The substitution rules are the same as for %Bind. For example, if the ReUse property is enabled, then the field is a true bind (':n' substituted). Otherwise, inline substitution occurs.

Example

You could use these functions in an Update statement within a Do Select action:

With a Do Select action, the increment/decrement occurs once per run, not once for every fetch. So unless your Do Select action implements the Reselect property, the value is changed only on the first iteration of the loop. Alternatively, with the Reselect property or Do While and Do Until actions, every iteration reruns the Select statement and then fetches one row. With these types of loops, the value changes on every iteration.

See Also

%Bind

Click to jump to top of pageClick to jump to parent topic%NoUpperCase

Description

Because the %NoUpperCase construct can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

See Also

%NoUppercase

Click to jump to top of pageClick to jump to parent topic%NumToChar

Description

Because the %NumToChar construct can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

See Also

%NumToChar

Click to jump to top of pageClick to jump to parent topic%OldKeyEqual

Description

Because the %OldKeyEqual construct can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

Note. This meta-SQL can only be used in PeopleCode programs, not in Application Engine SQL actions.

See Also

%OldKeyEqual

Click to jump to top of pageClick to jump to parent topic%ProcessInstance

Description

Use the %ProcessInstance meta-variable to specify the numeric (unquoted) process instance.

Click to jump to top of pageClick to jump to parent topic%ResolveMetaSQL

Syntax

%ResolveMetaSQL(&SQL,%DbType)

Description

The %ResolveMetaSQL function returns a string with any meta-SQL in the string expanded to platform-specific SQL, similar to the text that is returned on the Meta-SQL tab when using the Resolve Meta-SQL option in the SQL Editor.

If &SQL does not contain any meta-SQL, then the function returns a string identical to &SQL.

%DBType value represents the type of current database.

Parameters

&SQL

Specify a string containing the SQL to be resolved.

%DBType

%DBType value is DB2

For more information on valid values to %DBType:

See FetchSQL.

Returns

A string with meta-SQL expanded to platform-specific SQL.

Example

Here is an example:

&SQLText = FetchSQL(SQL.PTLT_CODE_MARKET); &ResolveSQLText = ResolveMetaSQL(&SQLText,DB2);

Suppose &SQLText contains the following SQL:

INSERT INTO %Table(PTLT_ASSGN_TASK)(PTLT_FEATURE_CODE , PTLT_TASK_CODE , PORTAL_NAME , PTLT_TASK_CODE2 , MENUNAME , OBJECTOWNERID) SELECT A.EOLT_FEATURE_CODE , %Sql(PTLT_TASK_CODE, A.PNLGRPNAME, A.MARKET) , 'EMPLOYEE' , %Sql(PTLT_TASK_CODE, A.PNLGRPNAME, A.MARKET) , A.MENUNAME , ' ' FROM %Table(EOLT_FEAT_COMP) A , %Table(PTLT_TASK) B , %Table(PTLT_TASK_LOAD) C WHERE %Sql(PTLT_TASK_CODE, A.PNLGRPNAME, A.MARKET) = B.PTLT_TASK_CODE AND B.PTLT_TASK_CODE = C.PTLT_TASK_CODE AND B.PTLT_LOAD_METHOD = C.PTLT_LOAD_METHOD AND A.MENUNAME <> ' ' AND A.MENUNAME <> C.MENUNAME AND NOT EXISTS ( SELECT 'X' FROM %Table(PTLT_ASSGN_TASK) Z WHERE Z.PTLT_FEATURE_CODE = A.EOLT_FEATURE_CODE AND Z.PTLT_TASK_CODE = %Sql(PTLT_TASK_CODE, A.PNLGRPNAME, A.MARKET))

&ResolveSQLText would contain the following text (depending on your database platorm):

INSERT INTO PS_PTLT_ASSGN_TASK(PTLT_FEATURE_CODE , PTLT_TASK_CODE , PORTAL_NAME , PTLT_TASK_CODE2 , MENUNAME , OBJECTOWNERID) SELECT A.EOLT_FEATURE_CODE , RTRIM(SUBSTR( A.PNLGRPNAME ,1 ,18)) || '.' || A.MARKET , 'EMPLOYEE' , RTRIM(SUBSTR( A.PNLGRPNAME ,1 ,18)) || '.' || A.MARKET , A.MENUNAME , ' ' FROM PS_EOLT_FEAT_COMP A , PS_PTLT_TASK B , PS_PTLT_TASK_LOAD C WHERE RTRIM(SUBSTR( A.PNLGRPNAME,1,18)) || '.' || A.MARKET = B.PTLT_TASK_CODE AND B.PTLT_TASK_CODE = C.PTLT_TASK_CODE AND B.PTLT_LOAD_METHOD = C.PTLT_LOAD_METHOD AND A.MENUNAME <> ' ' AND A.MENUNAME <> C.MENUNAME AND NOT EXISTS ( SELECT 'X' FROM PS_PTLT_ASSGN_TASK Z WHERE Z.PTLT_FEATURE_CODE = A.EOLT_FEATURE_CODE AND Z.PTLT_TASK_CODE = RTRIM(SUBSTR( A.PNLGRPNAME,1,18)) || '.' || A.MARKET)

See Also

Using the SQL Editor.

Click to jump to top of pageClick to jump to parent topic%ReturnCode

Description

Use the %ReturnCode meta-variable to evaluate or specify the return code of the last Application Engine program step performed. If the operation fails, breaks, or generates an error, %ReturnCode is set to one of the following types of return codes:

If the application process is not terminated, %ReturnCode is reset to the default value of 0 for each subsequent successful operation.

Click to jump to top of pageClick to jump to parent topic%RightParen

Description

Use the %RightParen meta-variable to specify a right parenthesis. Usage is similar to that of %Comma.

See Also

%Comma

%SQL

Click to jump to top of pageClick to jump to parent topic%Round

Description

Because the %Round function can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

See Also

%Round

Click to jump to top of pageClick to jump to parent topic%RoundCurrency

Syntax

%RoundCurrency( expression, [ALIAS.]currency_field)

Description

Use the %RoundCurrency function to return the value of an amount field rounded to the currency precision specified by the Currency Control Field property of the field, as defined in the Application Designer Record Field Properties dialog box. For this function to work, you must have the Multi-Currency option selected on the PeopleTools Options page.

See Using Administration Utilities.

This function is an enhanced version of the Application Engine &ROUND construct that appeared in previous releases, and it is valid only in Application Engine SQL; it is not valid for SQLExecs or view text.

You can use this function in the Set clause of an Update statement or the Select list of an Insert/Select statement. The first parameter is an arbitrary expression of numeric values and columns from the source tables that computes the monetary amount to be rounded. The second parameter is the control currency field from a particular source table (the Update table, or a table in the From clause of an Insert/Selectstatement). This field identifies the corresponding currency value for the monetary amount.

Note. Remember that the as of date of the Application Engine program is used for obtaining the currency rounding factor. The currency rounding factor is determined by the value of DECIMAL_POSITIONS in the corresponding row in PS_CURRENCY_CD_TBL, which is an effective-dated table.

If multicurrency is not in effect, the result is rounded to the precision of the amount field (either 13.2 or 15.3 amount formats are possible).

Example

UPDATE PS_PENDING_DST SET MONETARY_AMOUNT = %RoundCurrency( FOREIGN_AMOUNT * CUR_EXCHNG_RT, CURRENCY_CD) WHERE GROUP_BU = %Bind(GROUP_BU) AND GROUP_ID = %Bind(GROUP_ID)

Click to jump to top of pageClick to jump to parent topic%RunControl

Description

Use the %RunControl meta-variable to specify a quoted string containing the current run control identifier. The run control ID is available to your program when using %RunControl, regardless of whether the AEREQUEST table contains a row.

Click to jump to top of pageClick to jump to parent topic%Select

Syntax

%Select(statefield1[, statefield2]...[, statefieldN])

Select field1[, field2]...[, fieldN]

The statefields must be valid fields on the state record (they may be fieldname or recordname.fieldname, as with %Bind), and fields must be either valid fields in the From tables or hard-coded values.

Description

Use the %Select construct to identify the state record fields to hold the values returned by the corresponding Select statement. The %Select construct is required at the beginning of all Select statements. For example, you need one in the flow control actions and one in the SQL actions that contain a Select statement.

You use the %Select construct to pass variables to the state record, and you use the %Bind construct to retrieve or reference the variables.

Example

Consider the following sample statement:

%SELECT(BUSINESS_UNIT,CUST_ID) SELECT BUSINESS_UNIT, CUST_ID FROM PS_CUST_DATA WHERE PROCESS_INSTANCE = %BIND(PROCESS_INSTANCE)

The following steps illustrate the execution of the previous statement:

  1. Resolve bind variables.

    The string %Bind(PROCESS_INSTANCE) is replaced with the value of the state record field called PROCESS_INSTANCE.

  2. Execute the SQL Select statement.

  3. Perform a SQL Fetch statement.

    If a row is returned, the state record fields BUSINESS_UNIT and CUST_ID are updated with the results. If the Fetch statement does not return any rows, all fields in the %Select construct retain their previous values.

Note. All fields referenced by a %Select construct must be defined in the associated state record. Also, aggregate functions always return a row, so they always cause the state record to be updated. As such, for aggregate functions, no difference exists between using %SelectInit or %Select.

Click to jump to top of pageClick to jump to parent topic%SelectAll

Description

Because the %SelectAll shortcut can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

Note. This meta-SQL can only be used in PeopleCode programs, not in Application Engine SQL actions.

See Also

%SelectAll

Click to jump to top of pageClick to jump to parent topic%SelectByKey

Description

Because the %SelectByKey shortcut can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

Note. This meta-SQL can only be used in PeopleCode programs, not in Application Engine SQL actions.

See Also

%SelectByKey

Click to jump to top of pageClick to jump to parent topic%SelectByKeyEffDt

Description

Because the %SelectByKeyEffDt shortcut can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook

Note. This meta-SQL can only be used in PeopleCode programs, not in Application Engine SQL actions.

See Also

%SelectByKeyEffDt.

Click to jump to top of pageClick to jump to parent topic%SelectDistinct

Description

Because the %SelectDistinct shortcut can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

Note. This meta-SQL can only be used in PeopleCode programs, not in Application Engine SQL actions.

See Also

%SelectDistinct

Click to jump to top of pageClick to jump to parent topic%SelectInit

Syntax

%SelectInit(statefield1[, statefield2]...[, statefieldN])

Select field1[, field2]...[, fieldN]

The statefields must be valid fields on the state record (they may be fieldname or recordname.fieldname, as with %Bind), and fields must be either valid fields in the From tables or hard-coded values.

Description

Use the %SelectInit construct to identify the state record fields to hold the values returned by the corresponding Select statement.

The %SelectInit construct is identical to the %Select construct with the following exception: if the Select statement returns no rows, then %SelectInit re-initializes the buffers. In the case of a %Select construct where no rows are returned, the state record fields retain their previous values.

Note. For aggregate functions, no difference exists between using %SelectInit or %Select.

Click to jump to top of pageClick to jump to parent topic%Space

Description

Use the %Space meta-variable to specify a single space. Usage is similar to %Comma.

See Also

%Comma

%SQL

Click to jump to top of pageClick to jump to parent topic%SQL

Description

Use the %SQL construct to specify a SQL object, which replaces the %SQL construct in a statement. This construct enables commonly used SQL text to be shared among Application Engine and PeopleCode programs. In Application Engine, you use %Bind to specify bind variables. In PeopleCode SQL, you can use

:record.field

or

:1

If you create SQL objects that you plan to share between Application Engine and PeopleCode programs, the %SQL construct enables you to pass parameters for resolving bind variables without being concerned with the difference in the bind syntax that exists between Application Engine and PeopleCode. However, you must tailor the base SQL statement that uses %SQL to represent a shared object with binds to Application Engine or to PeopleCode.

When a SQL object specified has more than one version, the database type always takes precedence. That is:

Example

For example, assume that your SQL is similar to the following:

UPDATE PS_TEMP_TBL SET ACTIVE = %BIND(MY_AET.ACTIVE) WHERE PROCESS_INSTANCE = %ProcessInstance

This code would not be valid if the SQL ran in PeopleCode. However, if you define your SQL as shown, you could use parameters in %SQL to insert the appropriate bind variable:

UPDATE PS_TEMP_TBL SET ACTIVE = %P(1) WHERE PROCESS_INSTANCE = %ProcessInstance

From Application Engine, the base SQL, or source statement, might look like this:

%SQL(SQL_ID, %BIND(MY_AET.ACTIVE))

The PeopleCode SQL might look like this:

%SQL(SQL_ID, :MY_AET.ACTIVE)

Note. You can use %SQL only to reference SQL objects created directly in Application Designer. For instance, you cannot use %SQL to reference SQL that resides within a section in an application library. Common SQL should be stored as a proper SQL object.

See Also

%SQL

Click to jump to top of pageClick to jump to parent topic%SQLRows

Description

Use the %SQLRows meta-variable to specify whether a SQL action returned any rows.

You can use %SQLRows in any Application Engine SQL statement, but the underlying value is affected only by SQL actions. It is not affected by Do When, Do Select, Do While, and Do Until actions. For Select statements, the value can only be 0 or 1: row not found or rows found, respectively. It does not reflect the actual number of rows that meet the Where criteria. To find the number of rows that meet the Where criteria, code a Select Count (*) statement.

Click to jump to top of pageClick to jump to parent topic%Substring

Description

Because the %Substring function can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

See Also

%Substring

Click to jump to top of pageClick to jump to parent topic%Table

Syntax

%Table(recname)

Description

Use the %Table construct to return the SQL table name for the record specified with recname.

This construct can be used to specify temporary tables for running parallel Application Engine processes across different subsets of data.

Example

For example, the following statement returns the record PS_ABSENCE_HIST:

%Table(ABSENCE_HIST)

If the record is a temporary table and the current process has a temporary table instance number specified, then %Table resolves to that instance of the temporary table PS_ABSENCE_HISTnn, where nn is the instance number.

See Also

%Table

Click to jump to top of pageClick to jump to parent topic%Test

Description

Because the %Test construct can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

See Also

%Test

Click to jump to top of pageClick to jump to parent topic%TextIn

Description

Because the %TextIn construct can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

See Also

%TextIn

Click to jump to top of pageClick to jump to parent topic%TimeAdd

Description

Because the %TimeAdd construct can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

See Also

%TimeAdd

Click to jump to top of pageClick to jump to parent topic%TimeIn

Description

Because the %TimeIn construct can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

See Also

%TimeIn

Click to jump to top of pageClick to jump to parent topic%TimeNull

Description

Because the %TimeNull meta-variable can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

See Also

%TimeNull

Click to jump to top of pageClick to jump to parent topic%TimeOut

Description

Because the %TimeOut construct can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

See Also

%TimeOut

Click to jump to top of pageClick to jump to parent topic%TimePart

Description

Because the %TimePart function can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

See Also

%TimePart

Click to jump to top of pageClick to jump to parent topic%TrimSubstr

Description

Because the %TrimSubstr function can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

See Also

%TrimSubstr

Click to jump to top of pageClick to jump to parent topic%Truncate

Description

Because the %Truncate function can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

See Also

%Truncate

Click to jump to top of pageClick to jump to parent topic%TruncateTable

Syntax

%TruncateTable(table name)

Description

Use the %TruncateTable construct to invoke a bulk delete command on a table. This construct is functionally identical to a Delete SQL statement with no Where clause, but it is faster on databases that support bulk deletes. If you are familiar with COBOL, this construct is an enhanced version of the COBOL meta-SQL construct with the same name.

Some database vendors have implemented bulk delete commands that decrease the time required to delete all the rows in a table by not logging rollback data in the transaction log. For the databases that support these commands, Application Engine replaces %TruncateTable with Truncate Table SQL. For the other database types, %TruncateTable is replaced with Delete From SQL.

You should commit after the step that immediately precedes the step containing the %TruncateTable statement. In general, you should use this construct early in your Application Engine program as an initialization task. In addition, avoid using this meta-SQL when your Application Engine program is started from the PeopleCode CallAppEngine function.

Unlike the COBOL version, Application Engine determines if a commit is possible before making the substitution. If a commit is possible, Application Engine makes the substitution and then forces a checkpoint and commit after the delete runs successfully.

If a commit is not possible, Application Engine replaces the meta-SQL with a Delete From string. This string ensures restart integrity when your program runs against a database for which an implicit commit is associated with Truncate Table or for which rollback data is not logged.

For databases that either run an implicit commit for %TruncateTable or require a commit before or after this meta-SQL, replace %TruncateTable with an unconditional delete in the following circumstances:

Note. To use a record name as the argument for %TruncateTable (instead of an explicit table name), you must include a %Table meta-SQL function to resolve the unspecified table name. For example, to specify the record PO_WEEK as the argument, use the following statement: %TruncateTable(%Table(PO_WEEK)).

See Also

%Table

%TruncateTable

Click to jump to top of pageClick to jump to parent topic%Update

Description

Because the %Update shortcut can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

Note. This meta-SQL can only be used in PeopleCode programs, not in Application Engine SQL actions.

See Also

%Update

Click to jump to top of pageClick to jump to parent topic%UpdatePairs

Description

Because the %UpdatePairs construct can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

Note. This meta-SQL can only be used in PeopleCode programs, not in Application Engine SQL actions.

See Also

%UpdatePairs

Click to jump to top of pageClick to jump to parent topic%UpdateStats

Syntax

%UpdateStats(record name ,[HIGH/LOW])

For example,

%UpdateStats(PO_WRK1)

The default is LOW.

Description

Use the %UpdateStats construct to generate a platform-dependent SQL statement that updates the system catalog tables used by the database optimizer in choosing optimal query plans. Use this construct after your program has inserted large amounts of data into a temporary table that will be deleted before the end of the program run. This construct saves you from having to use dummy seed data for the temporary table and having to update statistics manually.

Notes About %UpdateStats

For databases that either run an implicit commit for %UpdateStats or require a commit before or after this meta-SQL, Application Engine skips %UpdateStats in the following circumstances:

The following table shows how the %UpdateStats construct is resolved by the supported database systems:

Database Function

Behavior

MSS %UpdateStats

Specifying LOW produces the statement

UPDATE STATISTICS ​tablename

Specifying HIGH produces the statement

UPDATE STATISTICS ​tablename WITH FULLSCAN

Sybase %UpdateStats

LOW and HIGH = UPDATE ALL STATISTICS tablename

Oracle %UpdateStats

Oracle uses DDL templates (in PSDDLMODEL) to determine SQL statements for %UpdateStats. Use DDLORA.DMS to change.

Specifying LOW produces the statement

execute DBMS_STATS.GATHER_TABLE_STATS (ownname=>⇒ 'PT8468908', tabname=>'PSSTATUS', estimate_percent=⇒ >20, method_opt=> 'FOR ALL INDEXED COLUMNS SIZE⇒ 1',cascade=>TRUE)

Specifying HIGH produces the statement

execute DBMS_STATS.GATHER_TABLE_STATS (ownname=>⇒ 'PT848908', tabname=>'PSSTATUS', estimate_percent=>⇒ dbms_stats.auto_sample_size, method_opt=> 'FOR ALL⇒ INDEXED COLUMNS SIZE 1',cascade=>TRUE)

DB2 UNIX %UpdateStats

In DB2 UNIX, %UpdateStats is performed by issuing sqlustat() calls that are equivalent to SQL statements. The sqlustat() is an internal DB2 API call function rather than an SQL command.

Specifying LOW is equivalent to issuing the statement

RUNSTATS ON TABLE ​tablename AND INDEXES ALL

Specifying HIGH is equivalent to issuing the statement

RUNSTATS ON TABLE ​tablename WITH DISTRIBUTION AND⇒ DETAILED INDEXES ALL

Note. You cannot view the sqlustat() calls nor the RUNSTATS statement in the SQL trace.

DB2 390 %UpdateStats

Uses a DDL model template (in PSDDLMODEL) to format a control statement for the DB2 UDB for OS390 and z/OS Runstats utility. Refer to the PeopleTools Installation Guide and the Administration Guide for more details on using %UpdateStats with DB2 UDB for OS390 and z/OS.

Specifying LOW produces the statement

RUNSTATS TABLESPACE [DBNAME].[TBSPCNAME] TABLE([⇒ DBNAME].[TABLE]) SAMPLE 25 [INDEXLIST] REPORT NO⇒ SHRLEVEL CHANGE UPDATE ACCESSPATH

Specifying HIGH produces the statement

RUNSTATS TABLESPACE [DBNAME].[TBSPCNAME] TABLE([⇒ DBNAME].[TABLE]) [INDEXLIST] REPORT NO SHRLEVEL⇒ CHANGE UPDATE ACCESSPATH

Informix %UpdateStats

Specifying LOW produces the statement

UPDATE STATISTICS MEDIUM FOR TABLE ​tablename

Specifying HIGH produces the statement

UPDATE STATISTICS HIGH FOR TABLE ​tablename

%UpdateStats Database Considerations

The following table lists potential issues that you might encounter when using %UpdateStats:

Database

Consideration

Microsoft SQL Server Sybase UDB

Application Engine forces a commit before and after the %UpdateStats statement.

Therefore, the system skips this meta-SQL if a commit is not allowed. For instance, a commit is not allowed in the following situations:

  • The Application Engine program is not running in batch mode.

  • You have issued non-Select/Fetch SQL (in which the data is likely to change) since the last commit.

  • You are deferring commits in a Select/Fetch loop within a restartable program.

Oracle

Oracle has an implicit commit after the %UpdateStats statement executes.

Same behavior as previous consideration.

DB2 UDB for OS/390 and z/OS

For DB2 UDB for OS/390 and z/OS, %UpdateStats requires IBM stored procedure DSNUTILS running in an authorized Work Load Manager Application Environment. It is also highly recommeded that individual tables intended to be a target of the %UpdateStats function are segregated to their own tablespaces. Refer to the following documents for more details on using %UpdateStats: PeopleTools Installation Guide for DB2 UDB for OS/390 and z/OS; PeopleTools Administration Guide for DB2 UDB for OS/390 and z/OS.

Note. You can trace information messages from the Runstats command on DB2 for z/os executed as a result of issuing %UpdateStats. To enable this trace, select the SQL Informational Trace check box on the Configuration Manager – Trace page.

Informix IBM UDB

%UpdateStats locks the table being analyzed on UDB and Informix. Therefore, use this meta-SQL only on tables that are not likely to be concurrently accessed by other applications and users. You might use %UpdateStats to analyze Application Engine dedicated temporary tables.

All

%UpdateStats consumes a large amount of time and database resources if run against very large tables. Therefore, analyze permanent data tables outside of application programs. Also, if temporary tables are likely to grow very large during a batch run, run the batch program only with %UpdateStats enabled to seed the statistics data or when the data composition changes dramatically.

Disabling %UpdateStats

You can disable %UpdateStats in the following ways:

Using %UpdateStats With COBOL

You can use the %UpdateStats construct from SQL embedded in COBOL programs. Use this syntax:

%UpdateStats(tablename)

When you issue this construct from PeopleTools, the parameter is record name.

Click to jump to top of pageClick to jump to parent topic%Upper

Description

Because the %Upper construct can be used in more than just Application Engine programs, it is documented in the PeopleTools 8.52: PeopleCode Language Reference PeopleBook.

See Also

%Upper