%ExecuteEdits meta-SQL element

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.

Parameters

Parameter	Description
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.

Parameter

Description

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)

Notes About %ExecuteEdits

Note the following points about the %ExecuteEdits function:

Consider performance carefully when using this function.

Prompt table and Translate table edits have a significant effect because they involve correlated subqueries. Run a SQL trace at runtime so that you can view the SQL generated by %ExecuteEdits. Look for opportunities to optimize it.
In general, %ExecuteEdits is best used on a temporary table.

If you must run it against a real application table, you should provide Where clause conditions to limit the number of rows to include only those that the program is currently processing. Process the rows in the current set at one time rather than row by row.
With %ExecuteEdits, you cannot use work records in a batch, set-based operation.

All higher-order key fields used by prompt table edits must exist in the record that your code intends to edit, and the field names must match exactly. For example,
```
%ExecuteEdits(%Edit_PromptTable, MY_DATA_TMP)  
```
The record MY_DATA_TMP contains the field STATE with a prompt table edit against PS_REGION_VW, which has key fields COUNTRY and REGION. The REGION field corresponds to STATE, and COUNTRY is the higher-order key. For %ExecuteEdits to work correctly, the MY_DATA_TMP record must contain a field called COUNTRY. The edited field (STATE) can use a different name because Application Engine always references the last key field (ignoring EFFDT).
In Application Engine, %ExecuteEdits uses the system date when performing comparisons with effective date (EFFDT); however, in some cases this date is not appropriate (Journal Edit, Journal Import, and so on). In these situations, use Journal Date when comparing with EFFDT. To override the use of the default system date with a selected field from a joined table, use %AsOfDateOvr. For example,
```
%ExecuteEdits(%AsOfDateOvr(alias.fieldname), %Bind(...)...)
```
Restrict the number and type of edits to the minimum required.

Do not edit fields that are known to be valid or that are given default values later in the process. Also consider using a separate record with edits defined specifically for batch or provide a list of fields to be edited.