Specifying SQL Actions

This is the default action type for the first action within a given step. Use this action to perform the following SQL commands on multiple rows:

• Update

• Insert

• Delete

• Select

Note:

Before you insert SQL (select View, SQL) into a SQL action within a new Application Engine program, you must have saved the program previously. Saving is required because the program name you use to save the definition is used to relate your program with the SQL objects you are about to create. The same is true for inserting PeopleCode.

With a SQL action, you use the SQL Editor to create and modify a SQL statement. Following are some examples of SQL statements:

%Select(AF_PERFM_AET.PREV_ASOF_DT)
SELECT %DateOut(ASOF_DT)
FROM PS_AF_FCST_SCHT%Bind(EPM_CORE_AET.TABLE_APPEND,NOQUOTES)
WHERE AFDEFN_ID = %Bind(AF_CORE_AET.AFDEFN_ID)
AND ASOF_DT = (SELECT MAX(ASOF_DT)
FROM PS_AF_FCST_SCHT%Bind(EPM_CORE_AET.TABLE_APPEND,NOQUOTES)
WHERE AFDEFN_ID = %Bind(AF_CORE_AET.AFDEFN_ID)
AND ASOF_DT < %Bind(AF_PERFM_AET.ASOF_DT))

Note:

If you intend to include multiple SQL statements within a single action, you should use the meta-SQL construct %EXECUTE. The previous sample SQL statement sample contains bind variables from a previous Application Engine action.

Note:

Application Engine framework has restricted the length of executable SQL statement. The maximum SQL length is 32768. Application Engine does not process statements longer than that and will return an error.

No Rows Property

In addition to the ReUse Statement property, the No Rows property is available for SQL actions. If the SQL (Insert, Update, or Delete) associated with the SQL action does not return any rows, you must specify what the Application Engine program should do.

For example, you could use the No Rows property when you insert into a temporary table and then intend to perform further operations on the inserted rows (provided that some rows meet the criteria). If the initial combination of Insert and Select statements provides no rows, you could save the program from having to re-select on the temporary table before executing another operation, or you could prevent the program from performing set operations on the table with no qualifying rows.

When you set the No Rows property, choose from the following values:

Field or Control	Description
Abort	The program terminates.
Section Break	Application Engine exits the current section immediately, and control returns to the calling step.
Continue	The program continues processing.
Skip Step	Application Engine exits the current step immediately and moves on to the next step. Application Engine ignores the commit for the current step at runtime. If the current step contains only one action, then use Skip Step only to bypass the commit.

Note:

Using the No Rows property in conjunction with a Truncate Table operation is unreliable. Some database platforms report zero rows affected for truncations, regardless of how many rows were in the table.