Using Application Engine Meta-SQL

Syntax

%Abs(x)

Description

Use the %Abs meta-SQL construct to return a decimal value equal to the absolute value of a number x.

Example

SELECT INVENTORY_CODE FROM INVENTORY_TABLE WHERE %ABS(NEW_AMOUNT - OLD_AMOUNT) > SOME_ALLOWED_VALUE

Description

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

Description

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

Description

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

Description

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

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.

Syntax

%BINARYSORT(Recname) 

Description

Any in-memory sorting performed using COBOL language functions is performed as a binary sort in the current character set used for COBOL processing, and may not necessarily match the sort order returned by the database in response to an Order By clause. Should you require the database to return data sorted using a binary sort of its encoding rather than the default linguistically-correct sort, you must use the %BINARYSORT meta-SQL function around each column in the Where or Order By clause where binary ordering is important.

However, for z/OS implementations, keep in mind that this binary sorting is only equivalent when the COBOL program is run z/OS server. For example, the binary sort produced in COBOL differs from the binary sort produced by the database, as the database is encoded in extended binary-coded decimal interchange code (EBCDIC) and the client is in an ASCII-based encoding. Therefore, %BINARYSORT should only be used in COBOL programs that are not run using the RemoteCall function, where the z/OS platform is not supported as a RemoteCall server.

When running against non-z/OS systems, %BINARYSORT can be used in both RemoteCall and non-RemoteCall programs.

Parameters

Example

SELECT RECNAME FROM PSRECDEFN  WHERE %BINARYSORT(RECNAME) < %BINARYSORT('xxx')

SELECT RECNAME FROM PSRECDEFN  ORDER BY %BINARYSORT(RECNAME)

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.

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

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

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

Parameters

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

INSERT INTO ps_greg
VALUES(%Bind(date_end))

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.

Syntax

%Cast(source_expr, source_type, target_type[, precision[.scale]])

Description

Use the %Cast meta-SQL function to convert a PeopleSoft data type to a Character data type. A database-generated error is returned if the function attempts to make an invalid conversion. %Cast can be used wherever %DateOut, %TimeOut, %DateTimeOut, %CurrentDateOut, %CurrentTimeOut, %CurrentDateTimeOut, and %NumToChar functions can be used.

On some platforms the meta-SQL functions %DateOut, %TimeOut, %DateTimeOut, %CurrentDateOut, %CurrentTimeOut and %CurrentDateTimeOut don’t return a Character value. On other platforms, these functions return a Character string only in certain cases. %Cast returns a Character value on all supported platforms.

Use %Cast only in the Select portion of query. Do not use it in a Where clause or in Insert or Update statements.

Parameters

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:

• The function must be located at the beginning of the statement.

• %ClearCursor can be the only function or command contained in the statement.

Parameters

Syntax

%COALESCE(expr1, expr2, ...)

Description

Use the %COALESCE function to return the first non-null argument provided to the function.

Parameters

Example

The following example uses the PRODUCT_INFO table to organize a clearance sale of products. It gives a 10 percent discount to all products with a list price. If there is no list price, the sale price is the minimum price. If there is no minimum price, the sale price is 10.

SELECT product_id, list_price, min_price, %COALESCE(0.9*list_price, min_price, 10) "Sale" 
from PRODUCT_INFO
where SUPPLIER_ID = 6009;

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.

Syntax

string1 %Concat string2

Description

At runtime, the %Concat meta-SQL variable is replaced by the string concatenation operator appropriate for the relational database management system (RDBMS) being used. For example, on DB2, the %Concat meta-SQL variable is replaced with CONCAT, while on SQL Server it's replaced with a +, and on Oracle it’s replaced with ||.

This meta-SQL variable is supported with the same limitations as the native concatenation operator for the RDBMS where the meta-SQL is being executed. For example, some platforms enable you to concatenate a string with a numeric value; others flag this as an error. PeopleTools makes no attempt to check or convert the data types of either of the operands.

Example

Example 1:

SELECT LAST_NAME %Concat ',' %Concat FIRST_NAME FROM PS_EMPLOYEE

Example 2:

SELECT PORTAL_NAME 
 , PORTAL_LABEL 
 , %TrimSubstr(PORTAL_OBJNAME,1,30) %Concat ':' %Concat %TrimSubstr(PORTAL_NAME,1,30) 
  FROM PSPRSMDEFN 
 WHERE PORTAL_PRNTOBJNAME = 'CO_NAVIGATION_COLLECTIONS' 
   AND PORTAL_REFTYPE = 'F'

Description

The %CurrentDateIn meta-SQL variable expands to a platform-specific SQL substring representing the current date in the Where clause of a SQL Select or Update statement, or when the current date is passed in an Insert statement.

Description

The %CurrentDateOut meta-SQL variable expands to platform-specific SQL for the current date in the Select clause of a SQL query.

Description

The %CurrentDateTimeIn meta-SQL variable expands to a platform-specific SQL substring representing the current datetime in the Where clause of a SQL Select or Update statement, or when the current date time is passed in an Insert statement.

Description

The %CurrentDateTimeOut meta-SQL variable expands to platform-specific SQL for the current datetime in the Select clause of a SQL query.

Description

The %CurrentTimeIn meta-SQL variable expands to a platform-specific SQL substring representing the current time in the Where clause of a SQL Select or Update statement, or when the current time is passed in an Insert statement.

Description

The %CurrentTimeOut meta-SQL variable expands to platform-specific SQL for the current time in the Select clause of a SQL query.

Syntax

%DateAdd(date_from, add_days)

Description

The %DateAdd meta-SQL function returns a date by adding add_days to date_from. The add_days variable can be negative.

Example

SQLExec("SELECT %DateAdd(%DateIn('2002-02-02'), 12) from %SelectDummyTable", &add);
WinMessage(&add);

Syntax

%DateDiff(date_from, date_to)

Description

The %DateDiff meta-SQL function returns an integer representing the difference between two dates in number of days. For example: diff = date_to - date_from

Example

%DateDiff(%DateIn('1966-06-30'), %DateIn('1997-01-01'))

%DateDiff( date1_column, date2_column)

%DateDiff( %DateAdd(date1_column, 30), date2_column)

The following usage is illegal (always use %Datein for inputting date literals):

%DateDiff('1996-06-30', '1997-01-01') /* should use %DateIn for inputting date literals */

Syntax

%DateIn(dt)

Description

The %DateIn meta-SQL variable expands into platform-specific SQL syntax for the date. Use %DateIn whenever a date literal or Date bind variable is used in a comparison in the Where clause of a Select or Update statement, or when a Date value is passed in an Insert statement.

UPDATE PS_PERSONAL_DATA SET LASTUPDT = %DATEIN('2002-12-11')

UPDATE PS_PERSONAL_DATA SET LASTUPDT = %DATEIN(:1)

Parameters

Syntax

%DateNull

Description

Use the %DateNull meta-SQL variable to specify a null value for a Date field. Only use this meta-SQL in Insert or Update clauses. Do not use this meta-SQL in a Where clause.

This meta-SQL resolves into a database-specific SQL substring, as shown in the following table:

Parameters

None.

Syntax

%DateOut(dt)

Description

The %DateOut meta-SQL variable expands to either a platform-specific SQL substring or datetime value, depending on the database platform, representing a datetime column in the Select clause of a SQL query

Parameters

Syntax

%DatePart(DTTM_Column)

Description

The %DatePart meta-SQL variable returns the date portion of the specified DateTime column.

%DateOut(%DatePart(DTTM_COLUMN)) from some_table

insert into some_table values(%DatePart(%DateTimeIn('2001-01-01-12.34.56.789012')))

Parameters

Syntax

%DateTimeDiff(datetime_from, datetime_to)

Description

The %DateTimeDiff meta-SQL function returns a time value, representing the difference between two date times in minutes.

Example

The following example returns the difference in hours between the current datetime and the requested datetime:

%DateTimeDiff(%CurrentDateIn, RQSTDTTM) < " | RECORD.FIELDNAME * 60;

The following example returns the difference in minutes:

%DateTimeDiff(%CurrentDateIn, RQSTDTTM) < " | RECORD.FIELDNAME;

Syntax

%DateTimeIn(dtt)

Description

The %DateTimeIn meta-SQL variable expands to platform-specific SQL for a DateTime value in the Where clause of a SQL Select or Update statement, or when a DateTime value is passed in an Insert statement.

UPDATE PS_PERSONAL_DATA SET LASTUPDTTM = %DATETIMEIN('2002-12-11-11.59.00.000000')

UPDATE PS_PERSONAL_DATA SET LASTUPDTTM = %DATETIMEIN(:1)

Parameters

Syntax

%DateTimeNull

Description

Use the %DateTimeNull meta-SQL variable to specify a null value for a DateTime field. Only use this meta-SQL in Insert or Update clauses. Do not use this meta-SQL in a Where clause.

This meta-SQL resolves into a database-specific SQL substring, as shown in the following table:

Parameters

None.

Example

%InsertSelect(LEDGER_KK_WK2,LEDGER_KK_WRK, CURRENCY_CD = %Bind(TO_CURRENCY) ,POSTED_TOTAL_AMT = SUM(POSTED_BASE_AMT),POSTED_TRAN_AMT = 0,POSTED_BASE_AMT = 0,BASE_CURRENCY = %Bind(TO_CURRENCY),PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE),DTTM_STAMP_SEC = %DateTimeNull)

FROM PS_LEDGER_KK_WRK 

WHERE PROCESS_INST_STG = %Bind(PROCESS_INSTANCE)

AND CURRENCY_CD <> %Bind(TO_CURRENCY)

GROUP BY PROCESS_INST_STG, BUSINESS_UNIT,LEDGER, ACCOUNT, %List(FIELD_LIST, CFCC1_AK_SBR) ,STATISTICS_CODE, FISCAL_YEAR,ACCOUNTING_PERIOD

Syntax

%DateTimeOut(datetime_col)

Description

The %DateTimeOut meta-SQL variable expands to either a platform-specific SQL substring or datetime value, depending on the database platform, representing a datetime column in the Select clause of a SQL query

Parameters

Syntax

%DecDiv(a,b)

Description

The %DecDiv meta-SQL function returns a number representing the value of a divided by b, where a and b are numeric expressions.

If the result needs to be picked up by a bind variable, pick it up using the Character type or PIC X(50).

Parameters

Example

%DecDiv(1000.0, :1) 

In the example, :1 is a bind variable in SQLExec PeopleCode.

Syntax

%DecMult(a,b)

Description

The %DecMult meta-SQL function returns a number representing a multiplied by b, where a and b are numeric expressions.

If the result needs to be picked up by a bind variable, pick it up using the Character type or PIC X(50).

Parameters

Example

%DecMult(12.3, 34.67) 
 
%DecMult(c1 + c2, c3) 
 

In the example, c1, c2, and c3 are fields of the Number data type.

Syntax

%Delete(:num)

Description

This is a shorthand for:

Delete from %Table(:num) where %KeyEqual(:num)

Syntax

%DTTM(date, time)

Description

The %DTTM meta-SQL function combines the database date in the date value with the database time in the time value and returns a database timestamp value.

Example

INSERT INTO TABLE1 (TIMESTAMP) SELECT %DTTM(DATE,TIME) FROM TABLE2

Syntax

%EffDtCheck(recordname [correlation_id1], correlation_id2, as_of_date)

Description

The %EffDtCheck construct expands into an effective date subquery suitable for a Where clause. The value for as_of_date is automatically wrapped in %DateIn unless as_of_date is already wrapped in %DateIn or refers to other database columns.

%EffDtCheck only works with effective dates. It does not take effective sequence numbers (EFFSEQ) into account. It also does not do effective-status (EFF_STATUS) checking.

Parameters

Example

The following is a generic code sample:

SELECT. . .    
   FROM. . . 
      WHERE %EffDtCheck(recordname correlation_id, as_of_date)

The example code resolves into the following:

SELECT . . . 
   FROM. . . 
   WHERE correlation_id.EFFDT = (SELECT MAX(EFFDT) FROM recordname 
      WHERE recordname.KEYFIELD1 = correlation_id.KEYFIELD1 
      AND recordname.KEYFIELD2 = correlation_id.KEYFIELD2 
      AND. . . 
      AND recordname.EFFDT <= %DATEIN(as_of_date))

In the following example, &Date has the value of 01/02/1998. The example &Rec object has an EFFDT key field.

SQLExec("SELECT FNUM FROM PS_REC A WHERE %EffDtCheck(:1, A, :2)", &Rec, &Date);

This example code resolves into the following:

"Select FNUM from PS_REC A where EFFDT = (select MAX(EFFDT)  
from PS_REC  
   where PS_REC.FNUM = A.FNUM  
   and PS_REC.EFFDT <= %DateIn('1998-01-02') )"

The following example uses correlation IDs:

SELECT A.DEPTID 
FROM %Table(DEPT_TBL) A 
WHERE 
%EffDtCheck(DEPT_TBL B, A, %CurrentDateIn) 
AND A.EFF_STATUS = 'A'

This example code resolves into the following:

SELECT A.DEPTID 
FROM %Table(DEPT_TBL) A 
WHERE 
A.EFFDT =  
(SELECT MAX(B.EFFDT) 
FROM DEPT_TBL B 
WHERE 
A.SETID = B.SETID 
AND A.DEPTID = B.DEPTID 
AND B.EFFDT <=%CurrentDateIn) 
AND A.EFF_STATUS = 'A'

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.

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;
/

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

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)

Syntax

%FirstRows(n)

Description

The %FirstRows meta-SQL variable is replaced by database-specific SQL syntax to optimize retrieval of n rows. Depending on the database, this variable optimizes:

• The query path.

• The number of rows returned.

• The number of rows returned per fetch buffer.

Parameters

Example

The following code checks for the existence of a row:

&SQL = CreateSQL("select %firstrows(1) 'x' from PS_EXAMPLE where COL1 = :1", &temp);

The following populates a 10-element array:

&SQL = CreateSQL("select %firstrows(10) COL2, COL3 from PS_EXAMPLE_VW where COL1 = :1", &temp);

Syntax

%GetNextSeqValue(sequence_name)

Description

The %GetNextSeqValue meta-SQL function increments the given sequence and returns its next value.

Notes about %GetNextSeqValue

Note the following points about the %GetNextSeqValue function:

• Use this function to generate order numbers, item numbers for lists, and so on.

• With this function, you can create new sequences or migrate GetNextNumberWithGapsCommit() sequences.

Example

SQLExec("SELECT %GetNextSeqValue(PS_ORDER_NUMBER) FROM %SelectDummyTable", &order_number);

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

Returns

A string containing the text of a PeopleCode program.

Example

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

Syntax

%Insert(:num)

Description

This is a shorthand for:

Insert into %Table(:num) (%List(Nonnull_Fields :num)) values (%InsertValues(:num))

Syntax

%InsertSelect([DISTINCT, ]insert_recname, select_recname [ correlation_id][, select_recname_n [ correlation_id_n]] [, override_field = value]. . .)  

Description

The %InsertSelect meta-SQL construct generates an Insert statement with a Select statement. It does not generate a From statement. You must specify the select records before you specify override fields.

The Insert column list is composed of all the fields in the specified insert_recname, with the exception of LongChar or Image fields.

The corresponding value in the Select list is generated based on the following precedence:

1. If the Insert fieldname appears as an override_field, the corresponding value is used in the Select list.

2. If the Insert field name matches a field name in one of the select_recname variables specified, the corresponding Select field is used in the Select list.

3. The search order of the select_recname records is the order that they are specified in the %InsertSelect function.

4. If the Insert field name has a constant default value defined in Application Designer, that value is used in the Select list.

5. A default value appropriate for the data type of the Insert field is used (blank for characters, zero for numbers, NULL for Date, Time, and DateTime values, and so on.)

Use the optional override_field variable to specify values for a particular field.

For each field you specify, the matching logic described in the preceding list is not performed. Instead, the value that you specify after the equal sign is used for that field in the actual Select list. Use this technique to let PeopleTools or Application Engine handle most of the fields in the record, while specifying some of them explicitly. Also, you can use override_field to specify aggregate functions like Sum, Max, and so on.

Parameters

Example

Here is a basic example:

%InsertSelect(AE_SECTION_TBL, AE_STEP_TBL S, AE_SECTION_TYPE = ' ')  
   FROM PS_AE_STEP_TBL S, PS_AS_STMT_TBL T  
WHERE. . .

The example code resolves into the following:

INSERT INTO PS_AE_SECTION_TBL (AE_APPLID, AE_SECTION,. . ., AE_SECTION_TYPE) 
SELECT S.AE_APPL_ID, S.AE_SECTION, . . . ' ' 
FROM PS_AE_STEP_TBL S, PS_AS_STMT_TBL T  
   WHERE. . .

In the following example, you have a temporary table, PS_MY_TEMP, which is based on a join between two other tables, PS_MY_TABLE1 and PS_MY_TABLE2:

%InsertSelect(MY_TEMP, MY_TABLE1, MY_TABLE2 T2) 
   FROM PS_MY_TABLE1 T1, PS_MY_TABLE2 T2 
WHERE %Join(COMMON_KEYS, MY_TABLE1 T1, MY_TABLE2 T2) . . .

This code resolves into:

INSERT INTO PS_MY_TEMP (FIELD1, FIELD2 . . .) 
   SELECT T2.FIELD1, T2.FIELD2, . . . 
FROM PS_MY_TABLE1 T1, PS_MYTABLE2 T2 
WHERE T1.FIELD1 = T2.FIELD1  
AND T1.FIELD2 = T2.FIELD2 . . .

The following example creates a distinct Select statement.

%InsertSelect(DISTINCT, MY_TABLE, TABLE1, TABLE2 T2) 
   FROM PS_TABLE1 T1, PS_TABLE2 T2 
WHERE %Join(COMMON_KEYS, TABLE1 T1, TABLE2 T2) . . .

This code resolves into:

INSERT INTO PS_MYTABLE (FIELD1, FIELD2 . . .) 
   SELECT DISTINCT T2.FIELD1, T2.FIELD2, . . . 
FROM PS_TABLE1 T1, PS_TABLE2 T2 
WHERE T1.FIELD1 = T2.FIELD1  
AND T1.FIELD2 = T2.FIELD2 . . .

Syntax

%InsertSelectWithLongs([DISTINCT, ]insert_recname, select_recname [ correlation_id][, select_recname_n [ correlation_id_n]] [, override_field = value]. . .)  

Description

The %InsertSelectWithLongs meta-SQL construct generates an Insert statement with a Select statement. It does not generate a From statement. You must specify the select records before you specify override fields.

Use %InsertSelectWithLongs instead of %InsertSelect when the fields in insert_recname include long values (LongChar and Image fields).

The Insert column list is composed of all the fields in the specified insert_recname.

The corresponding value in the Select list is generated based on the following precedence:

1. If the Insert fieldname appears as an override_field, the corresponding value is used in the Select list.

2. If the Insert field name matches a field name in one of the select_recname variables specified, the corresponding Select field is used in the Select list.

3. The search order of the select_recname records is the order that they are specified in the %InsertSelectWithLongs function.

4. If the Insert field name has a constant default value defined in Application Designer, that value is used in the Select list.

5. A default value appropriate for the data type of the Insert field is used (blank for characters, zero for numbers, NULL for Date, Time, and DateTime values, and so on.)

Use the optional override_field variable to specify values for a particular field.

For each field you specify, the matching logic described in the preceding list is not performed. Instead, the value that you specify after the equal sign is used for that field in the actual Select list. Use this technique to let PeopleTools or Application Engine handle most of the fields in the record, while specifying some of them explicitly. Also, you can use override_field to specify aggregate functions like Sum, Max, and so on.

Parameters

Example

Here is a basic example:

%InsertSelectWithLongs(AE_SECTION_TBL, AE_STEP_TBL S, AE_SECTION_TYPE = ' ')  
   FROM PS_AE_STEP_TBL S, PS_AS_STMT_TBL T  
WHERE. . .

The example code resolves into the following:

INSERT INTO PS_AE_SECTION_TBL (AE_APPLID, AE_SECTION,. . ., AE_SECTION_TYPE) 
SELECT S.AE_APPL_ID, S.AE_SECTION, . . . ' ' 
FROM PS_AE_STEP_TBL S, PS_AS_STMT_TBL T  
   WHERE. . .

In the following example, you have a temporary table, PS_MY_TEMP, which is based on a join between two other tables, PS_MY_TABLE1 and PS_MY_TABLE2:

%InsertSelectWithLongs(MY_TEMP, MY_TABLE1, MY_TABLE2 T2) 
   FROM PS_MY_TABLE1 T1, PS_MY_TABLE2 T2 
WHERE %Join(COMMON_KEYS, MY_TABLE1 T1, MY_TABLE2 T2) . . .

This code resolves into:

INSERT INTO PS_MY_TEMP (FIELD1, FIELD2 . . .) 
   SELECT T2.FIELD1, T2.FIELD2, . . . 
FROM PS_MY_TABLE1 T1, PS_MYTABLE2 T2 
WHERE T1.FIELD1 = T2.FIELD1  
AND T1.FIELD2 = T2.FIELD2 . . .

The following example creates a distinct Select statement.

%InsertSelectWithLongs(DISTINCT, MY_TABLE, TABLE1, TABLE2 T2) 
   FROM PS_TABLE1 T1, PS_TABLE2 T2 
WHERE %Join(COMMON_KEYS, TABLE1 T1, TABLE2 T2) . . .

This code resolves into:

INSERT INTO PS_MYTABLE (FIELD1, FIELD2 . . .) 
   SELECT DISTINCT T2.FIELD1, T2.FIELD2, . . . 
FROM PS_TABLE1 T1, PS_TABLE2 T2 
WHERE T1.FIELD1 = T2.FIELD1  
AND T1.FIELD2 = T2.FIELD2 . . .

Syntax

%InsertValues(recname)

Description

The %InsertValues meta-SQL construct produces a comma-separated list of the record's non-null field values. Input processing is applied to the fields in the following ways:

• If the field is a Date, a Time, or a DateTime data type, its value is automatically wrapped in %Datein, %TimeIn, or %DateTimeIn, respectively.

• If the field is a string, its value is automatically wrapped in quotation marks.

• If the field has a null value, it is not included in the list.

Parameters

Example

Here's an example:

SQLExec("Insert into TABLE (%List(NonNull_Fields, :1)) values (%InsertValues(:1))", &Rec);

This example code is expanded into:

"Insert into TABLE (FNUM, FCHAR, FDATE) values (27, 'Y', %datein('1989-11-27'))"

Description

Use the %IsRunningOnline meta-variable to determine whether the current Application Engine program is running in online mode or batch mode.

Returns

If %IsRunningOnline = 'N', then the current Application Engine program is running in batch mode.

If %IsRunningOnline = 'Y', then the current Application Engine program is running in online mode.

Examples

%Select(FS_BP_WRK_AET.SELECT_FLAG)
 SELECT 'X'
  FROM PS_INSTALLATION
 WHERE %IsRunningOnline = 'N'

%Select(FS_BP_WRK_SET.SELECT_FLAG
 SELECT 'X'
  FROM PS_INSTALLATION
 WHERE %IsRunningOnline = 'Y'

Syntax

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

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

field1 [, field2]. . .

Description

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

Parameters

Example

Here is an example:

%Join(COMMON_KEYS, PSAESECTDEFN ABC,  PSAESTEPDEFN XYZ) 

The example code results in the following being generated:

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

Here's another example:

%Join(COMMON_FIELDS, PSAEAPPLDEFN ABC,  PSAESECTDEFN XYZ) 

The second example results in the following being generated:

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

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

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

This example results in the following being generated:

ABC.AE_APPLID = XYZ.AE_APPLID

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

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

Description

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

Syntax

%KeyEqual(recname [ correlation_id])

Description

The %KeyEqual meta-SQL construct expands into a conditional phrase suitable for use in a Where clause.

The conditional phrase consists of a conjunction (AND) of [correlation_id.]keyfieldname = 'keyfieldvalue' phrases for each key field of the given record.

No auto-update processing is done, but other input processing is applied to the values, according to the following:

• If the field is a Date, a Time, or a DateTime data type, its value is automatically wrapped in %Datein, %TimeIn, or %DateTimeIn, respectively.

• If a value is a string, its value is automatically wrapped in quotation marks.

• If a value is NULL, the "=value" part is replaced with "IS NULL".

Example

Suppose that the record &REC has three keys: FNUM, FDATE, and FSMART. Here is a code example:

Local record &REC; 
 
&REC = CreateRecord(RECORD.MYRECORD); 
&REC.FNUM.Value = 27; 
&REC.FDATE.Value = %Date; 
SQLExec("Delete from MYRECORD A where %KeyEqual(:1 A)", &REC);

This example expands to:

"Delete from TABLE A  
   where A.FNUM = 27 
   AND A.FDATE = %Date('1989-11-27') 
   AND A.FSMART IS NULL" 

Syntax

%KeyEqualNoEffDt(recname [ correlation_id])

Description

The %KeyEqualNoEffDt meta-SQL construct expands into a conditional phrase suitable for use in a Where clause.

The conditional phrase consists of a conjunction (AND) of [correlation_id.]keyfieldname = 'keyfieldvalue' phrases for all key fields of the given record, except that it omits any key field named EFFDT.

No auto-update processing is done, but other input processing is applied to the values as follows:

• If the field is a Date, a Time, or a DateTime data type, its value is automatically wrapped in %Datein, %TimeIn, or %DateTimeIn, respectively.

• If a value is a string, its value is automatically wrapped in quotation marks.

• If a value is NULL, the "=value" part is replaced with "IS NULL."

Parameters

Example

The EMPL_CHECKLIST record has three keys: EMPLID, CHECK_SEQ, and EFFDT. Here is a code example:

&REC = CreateRecord(EMPL_CHECKLIST);

SQLExec("Delete from TABLE A where %KeyEqualNoEffdt(:1 A)", &REC)

The example expands to:

"Delete from TABLE A  
   where A.EMPLID = 8001 
   AND A.CHECK_SEQ = 00001" 

Description

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

Syntax

%Like("Literal")

Description

The %Like construct expands to look for literal values. This meta-SQL should be used when looking for like values. A percent sign character (%) is appended to literal.

If you're using a bind marker (such as ":1") for the literal argument in a SQLExec, you must wrap the SQL string with the ExpandSqlBinds function. ExpandSqlBinds replaces bind markers with the actual input values.

%Like generates the following:

like 'literal%'

If the literal value contains a backslash character (\) or percent sign (%), then %Like generates the following:

like 'literal%' escape '\'

See ExpandSqlBinds.

select  *  from  t1 Where c like 'ABCD'

Parameters

Syntax

%LikeExact(fieldname, "Literal")

Description

The %LikeExact meta-SQL variable expands to look for literal values. Use this variable when exact matches are necessary, taking into account wildcards in the literal values.

%LikeExact generates one of the following:

• If the literal contains no wildcards:

  fieldname = 'literal'

• If the literal ends with the '%' wildcard:

  fieldname like 'literal' [escape '\']

Some platforms require that you use RTRIM to get the correct value. The following characters are wildcards even when preceded with the backslash (\) escape character.

• %

• _

Therefore, on some platforms, the literal must end with a percent sign (%) wildcard that isn't preceded by a backslash (\). Here are some examples:

• literal = 'ABC%'

  You do not need RTRIM on any platform.

• literal = 'ABC\%'

  You need RTRIM on Microsoft SQL Server and DB2.

SQLExec("UPDATE PS_AE_APPL_TMP SET AE_PRODUCT = 'X' WHERE %LIKEEXACT(AE_APPL_ID, :1)", "AB\_C");

Parameters

Example

Here is an example:

UPDATE PS_AE_APPL_TMP SET AE_PRODUCT = 'X' WHERE %LIKEEXACT(AE_APPL_ID, 'ABC')

The example resolves into the following:

UPDATE PS_AE_APPL_TMP SET AE_PRODUCT = 'X' WHERE AE_APPL_ID = 'ABC'

Here is an example:

UPDATE PS_AE_APPL_TMP SET AE_PRODUCT = 'X' WHERE %LIKEEXACT(AE_APPL_ID, 'AB%C')

The example resolves into the following:

UPDATE PS_AE_APPL_TMP SET AE_PRODUCT = 'X' WHERE RTRIM(AE_APPL_ID) LIKE 'AB%C'

Here is an example:

UPDATE PS_AE_APPL_TMP SET AE_PRODUCT = 'X' WHERE LIKEEXACT(AE_APPL_ID, 'AB%C%')

The example resolves into the following:

UPDATE PS_AE_APPL_TMP SET AE_PRODUCT = 'X' WHERE AE_APPL_ID LIKE 'AB%C%'

Here is an example:

UPDATE PS_AE_APPL_TMP SET AE_PRODUCT = 'X' WHERE %LIKEEXACT(AE_APPL_ID, 'AB%C% ')

The example resolves into the following:

UPDATE PS_AE_APPL_TMP SET AE_PRODUCT = 'X' WHERE AE_APPL_ID LIKE 'AB%C% '

The following example shows using ExpandSqlBinds:

SQLExec(ExpandSqlBinds("SELECT COUNT(*) FROM PS_ITEM WHERE %LIKEEXACT(BUSINESS_UNIT, :1)", "M04"), %COUNT);

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.

Parameters

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))

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.

Parameters

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))

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.

Parameters

Example

UPDATE PS_TEMP

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

Syntax

%Mod(a, b)

Description

Use the %Mod meta-SQL function to return the remainder (or modulo) of division of one number by another number. %Mod uses the integer portion of both the dividend and the divisor. If the divisor is 0, %Mod returns the dividend value.

Example

Each of the following examples shows the computed result of the %Mod function:

%Mod(10, 3) = 1
%Mod(9, 3) = 0
%Mod(10.1, 3) = 1
%Mod(-10, 3) = -1
%Mod(10, 0)= 10

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:

• Do Select action

  %SELECT(field1, field2, ...) SELECT key1, key2, ... FROM PS_TABLE WHERE ... 
  ORDER BY key1, key2, ..."

• SQL

  UPDATE PS_TABLE SET SEQ_NBR = %Next(seq_field) WHERE key1 = %Bind(field1) 
  AND key2 = %Bind(field2) ...

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.

Syntax

%NoUppercase

Description

When processing a SQL statement, the system automatically casts all field names and possibly record names to uppercase when processing a SQL statement. When processing records from a third party, fields that are lowercase are cast into uppercase, which can create a runtime issue on case-sensitive platforms.

To prevent this, use the %NoUppercase meta-SQL statement at the beginning of the SQL statement.

Parameters

None.

Note there are not parameters, as well as no parenthesis, for this meta-SQL.

Returns

None.

Example

%NoUppercase  
INSERT INTO PS_RM_APP_ENG_LOG (MAP_ID  
, RECNAME  
, FIELDNAME  
, MESSAGE_SET_NBR  
, MESSAGE_NBR  
, LANGUAGE_CD)  
SELECT %Bind(MAP_ID)  
, %Bind(RECNAME)  
, ' '  
,17834  
, 1116  
, %Bind(LANGUAGE_CD)  
FROM %SelectDummyTable  
WHERE EXISTS (  
SELECT 'X'  
FROM SW_OPPORTUNITY SW_OPPORTUNITY  
, SW_PERSON SW_PERSON  
, SW_CUSTOMER SW_CUSTOMER  
, SW_SALES_TEAM_VW SW_SALES_TEAM_VW  
WHERE SW_OPPORTUNITY.SWCUSTOMERID = SW_CUSTOMER.SWCUSTOMERID  
AND SW_OPPORTUNITY.SWSALESTEAMID = SW_SALES_TEAM_VW.SWPROVIDERGRPID  
AND SW_SALES_TEAM_VW.SWPERSONID = SW_PERSON.SWPERSONID  
GROUP BY SW_OPPORTUNITY.SwOpportunityId  
HAVING COUNT(*) > 1)

Syntax

%NumToChar(Number)

Description

Use the %NumToChar construct to transform a numeric value into a character value. Spaces are trimmed from Number.

Syntax

%OldKeyEqual(recname [correlation_id])

Description

The %OldKeyEqual meta-SQL construct is similar to the %KeyEqual construct, except that it uses the original values of the record fields, rather than the current values. Since the rules for which values are original and which are current are not very clear, especially for standalone record objects, avoid using this meta-SQL construct. You should use separate records to hold previous values. This can make your code clearer and more maintainable.

Description

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

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

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 platform):

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)

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:

• Database (SQL) call errors.

• PeopleCode function errors.

• GEN_ERROR, when produced by general runtime exceptions.

• AE_ABORT, when produced by application or runtime logic, including some memory-related errors.

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

Description

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

Syntax

%Round(expression, factor)

Description

%Round rounds an expression to a specified scale before or after the decimal point. If factor is a literal, it can be rounded to a negative number.

Parameters

Example

Here is an example:

%Round(10.337, 2) = 10.34

%Round(13.67, 0) = 14

SQLExec("SELECT %Round(field_c1, :1) from RECORD_T", field_c2, &Result);

In the example, field_c1 and field_c2 are two fields in the record.

The following cases are illegal, and may cause incorrect results or runtime SQL errors:

%Round(10.337, 2 + 1)  (factor can not be an expression)

%Round(field_c1, field_c2) (factor can not be database columns)

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.

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)

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.

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.

Syntax

%SelectAll(:num [ correlation _id])

Description

%SelectAll is shorthand for selecting all fields in the specified record, wrapping DateTime fields with %DateOut, %TimeOut, and so on.

The pseudocode looks like this:

Select(AllFields, :num correlation_id) from %Table(:num) prefix

This shortcut is only appropriate if the statement is being used in PeopleCode or Application Engine to read data into memory. Dynamic views should retain the internal database formats for DateTime fields.

    &REC_PROJ_FUNDING = CreateRecord(Record.PROJ_FUNDING); /* free standing record
 object */ 
    /* Create SQL objects */ 
    &SQL_PROJ_FUNDING_SEL = CreateSQL("%SelectAll(:1)" /* bind this later */); 
        /* bind the %SelectAll */ 
        &SQL_PROJ_FUNDING_SEL.Execute(&REC_PROJ_FUNDING); 
    While &SQL_PROJ_FUNDING_SEL.Fetch(&REC_PROJ_FUNDING);
		/* Process row content ... /*
		End-While; 

Syntax

%SelectByKey(:num [ correlation_id ])

Description

This is a shorthand for:

Select %List(Select_List, :num correlation_id) from %Table(:num) correlation_id where %KeyEqual(:num, correlation_id)

Syntax

%SelectByKeyEffDt(:num1, :num2)

Description

This is a shorthand for:

Select %List(Select_List, :num1) from %Table(:num1) A where %KeyEqualNoEffDt(:num1 A) and %EffDtCheck(:num1 B, A, :num2)

Syntax

%SelectDistinct(:num [ prefix])

Description

%SelectDistinct is shorthand for selecting all fields in the specified record, wrapping DateTime fields with %DateOut, %TimeOut, and so on.

The pseudocode looks like this:

Select DISTINCT(AllFields, :num correlation_id) from %Table(:num) prefix

This shortcut is only appropriate if the statement is being used in PeopleCode or Application Engine to read data into memory. Dynamic views should retain the internal database formats for DateTime fields.

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.

Description

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

Description

Use the %SelectDummyTable variable to perform a SELECT without specifying a specific table. The database platform-specific “dummy table” is substituted in the SELECT.

Example

Before: In the following example, the SELECT was performed on the one-row PeopleTools installation table.

SELECT 'x' 
  FROM PS_INSTALLATION WHERE ...

After: Using the %SelectNoTable variable ensures that the SQL will not fail if the table does not exist or if the table contains more than one row.

SELECT 'x' 
  FROM %SelectDummyTable WHERE ...

Before: In the following example, %SelectInit is used to initialize files in an Application Engine state record.

%SelectInit(GL_JP_AET.PROCESS_STATUS, GL_JP_AET.PROCESS_ORIG, GL_LOG_MSG_AET.MESSAGE_SET_NBR, GL_LOG_MSG_AET.MESSAGE_NBR, GL_LOG_MSG_AET.MESSAGE_PARM1, GL_LOG_MSG_AET.MESSAGE_PARM2, GL_LOG_MSG_AET.MESSAGE_PARM3) 
 SELECT 'P' 
 , 'P' 
 , 5830 
 , 4 
 , TO_CHAR(1) 
 , 'DVP1' 
 , 'EK' 
  FROM PS_INSTALLATION

After: Using the %SelectNoTable variable ensures that the SQL will not fail if the table does not exist or if the table contains more than one row.

%SelectInit(GL_JP_AET.PROCESS_STATUS, GL_JP_AET.PROCESS_ORIG, GL_LOG_MSG_AET.MESSAGE_SET_NBR, GL_LOG_MSG_AET.MESSAGE_NBR, GL_LOG_MSG_AET.MESSAGE_PARM1, GL_LOG_MSG_AET.MESSAGE_PARM2, GL_LOG_MSG_AET.MESSAGE_PARM3) 
 SELECT 'P' 
 , 'P' 
 , 5830 
 , 4 
 , TO_CHAR(1) 
 , 'DVP1' 
 , 'EK' 
  FROM %SelectDummyTable

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.

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.

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 ...)

Syntax

%SqlHint(SQL_cmd, index, hint_text, DB_platform [,
{ENABLE | DISABLE}])

Description

Use the %SqlHint function to insert a database platform-specific SQL hint into the specified SQL statement. The hint is inserted immediately after the SQL command specified by the SQL_cmd parameter.

This meta-SQL function is ignored in any of the following circumstances:

• The current database connection does not match the DB_platform parameter.

• The DB_platform parameter is not specified as ORACLE. (This is a limitation of the current release.)

• The nth occurrence of the SQL command specified by SQL_cmd and index does not exist in the current SQL statement.

Parameters

Example 1

Before: The following example includes an Oracle-specific SQL hint to be inserted after the first SELECT of the SQL statement:

%SqlHint(SELECT, 1, '/*+ FIRST_ROWS(10) */', ORACLE) 
 SELECT EMPLID 
  FROM PS_JOB

After: For an Oracle connection, this meta-SQL would expand to:

 SELECT '/*+ FIRST_ROWS(10) */' EMPLID 
  FROM PS_JOB

After: On all other connections, this meta-SQL would expand to:

 SELECT EMPLID 
  FROM PS_JOB

Example 2

Before: In the following example, %SqlHint functions will be expanded and applied after all other meta-SQL expansion has occurred. In this example, the APPEND hint will be applied to the first INSERT found in this SQL statement. The LEADING hint will be applied to the first SELECT found in this SQL statement.

%SqlHint(INSERT, 1, '/*+ APPEND*/', ORACLE, ENABLE ),%SqlHint(SELECT, 1, '/*+ LEADING(H) INDEX(L, PSFJRNL_LN) */', ORACLE, ENABLE) %InsertSelect(JRNL_LIU_TAO, JRNL_LN L, BUSINESS_UNIT_IU=H.BUSINESS_UNIT_IU, LEDGER_GROUP=H.LEDGER_GROUP, IU_SYS_TRAN_CD=H.IU_SYS_TRAN_CD, IU_TRAN_CD=H.IU_TRAN_CD, PROCESS_INSTANCE=%Bind(PROCESS_INSTANCE)) 
  FROM %Table(JRNL_HIU_TAO) H, PS_%Bind(GL_JEDIT_WK_AET.RECNAME_JRNL_LN,NOQUOTES) L 
 WHERE H.PROCESS_INSTANCE=%Bind(PROCESS_INSTANCE) 
   AND H.BUSINESS_UNIT=L.BUSINESS_UNIT 
   AND H.JOURNAL_ID=L.JOURNAL_ID 
   AND H.JOURNAL_DATE=L.JOURNAL_DATE 
   AND H.UNPOST_SEQ=L.UNPOST_SEQ;

After: The SQL statement after all meta-SQL expansion and hint insertion:

 INSERT /*+ APPEND */ INTO PS_JRNL_LIU_TAO5 (BUSINESS_UNIT , JOURNAL_ID , JOURNAL_DATE 

/* For the purposes of clarity, many columns in this column list have been omitted from this example. */

 , DEPTID , SCENARIO , BUSINESS_UNIT_IU) 
 SELECT /*+ LEADING(H) INDEX(L, PSFJRNL_LN) */ L.BUSINESS_UNIT 
 , L.JOURNAL_ID 
 , L.JOURNAL_DATE 

/* For the purposes of clarity, many columns in this column list have been omitted from this example. */ 

 ,L.DEPTID 
 ,L.SCENARIO 
 , H.BUSINESS_UNIT_IU 
  FROM PS_JRNL_HIU_TAO5 H 
  , PS_%Bind(GL_JEDIT_WK_AET.RECNAME_JRNL_LN,NOQUOTES) L 
 WHERE H.PROCESS_INSTANCE=%Bind(PROCESS_INSTANCE) 
   AND H.BUSINESS_UNIT=L.BUSINESS_UNIT 
   AND H.JOURNAL_ID=L.JOURNAL_ID 
   AND H.JOURNAL_DATE=L.JOURNAL_DATE 
   AND H.UNPOST_SEQ=L.UNPOST_SEQ ;

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.

Syntax

%Substring(source_str, start, length)

Description

%Substring expands to a substring of source_str.

Parameters

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.

Syntax

%Test(Prefix, Test, Suffix)

Description

The %Test construct can be used with records that have no key values.

Parameters

Returns

If the expansion of Test produces only a blank (or empty) string, the entire %Test meta-SQL construct is replaced with an empty string. Otherwise, the %Test meta-SQL construct is replaced by the prefix, then the expansion of Test, and then the suffix.

Example

The following meta-SQL generates valid SQL even when the given record has no keys:

%SelectAll(:1) %Test(WHERE ,%KeyEqual(:1));

Syntax

%TextIn(BindVariable)

Description

%TextIn construct, when used with a bind variable, allows the insertion and updating of a text string into a LongChar field (column).

This construct is mandatory for any LongChar field insertion or update to be compatible on all database platforms on which it is supported.

Parameters

Example

In the following example, :1 is a bind variable in PeopleCode:

&String1 = "This is a test."

SqlExec("INSERT INTO PS_TABLE1 (STMTID, SQLSTMT) VALUES (1, %TextIn(:1))", &String1)

Syntax

%TimeAdd(datetime, add-minutes)

Description

This construct generates the SQL that adds add-minutes (a positive or negative integer literal or expression, provided that the expression resolves to a data type that can be used in datetime arithmetic for the given RDBMS) to the provided datetime (which can be a datetime literal or expression).

Parameters

Example

SELECT %TimeAdd(%CurrentTimeIn, 60) FROM %SelectNoTable

Syntax

%TimeNull

Description

Use this meta-SQL to specify a null value for a time field. Only use this meta-SQL in Insert or Update statements. Do not use this meta-SQL in a Where clause.

This meta-SQL resolves into a database-specific SQL substring, as shown in the following table:

Parameters

None.

Syntax

%TimeOut(time_col)

Description

The %TimeOut meta-SQL variable expands to either a platform-specific SQL substring or datetime value, depending on the database platform, representing the time_col column in the Select clause of a SQL query.

Parameters

Syntax

%TimePart(DTTM_Column)

Description

%TimePart returns the time portion of the specified datetime column.

%TimeOut(%TimePart(DTTM_COLUMN)) from some_table

insert into some_table values(%TimePart(%DateTimeIn('2001-01-01-12.34.56.789012')))

Parameters

Syntax

%TrimSubstr(source_str, start, length)

Description

%TrimSubstr, like %Substring, expands to a substring of source_str, except that trailing blanks are removed from the substring.

Syntax

%Truncate(expression, factor)

Description

%Truncate truncates an expression to a specified scale before or after the decimal point.

%Truncate(10.337, 2 + 1)  

Parameters

Example

Here is an example:

%Truncate(10.337, 2) = 10.33

%Truncate(13.37, 0) = 13

%Truncate(19.337, -1) = 10

SQLExec("SELECT %Truncate(field_c1, :1) from RECORD_T", field_c2, &Result);

In the example, field_c1 and field_c2 are two fields in the record.

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:

• A commit is not allowed, as in an Application Engine program called from PeopleCode.

• The program issues a non-select SQL statement since the last commit occurred. In such a situation, data is likely to have changed.

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

Syntax

%Update(:num [ , :num2 ])

Description

This is a shorthand for:

Update %Table(:num) set %UpdatePairs(:num) where %KeyEqual(:num2)

If num2 is omitted, the value defaults to num.

Syntax

%UpdatePairs(recname [correlation_id])

Description

The %UpdatePairs construct produces a comma-separated list of fieldname = 'fieldvalue' phrases for each changed field of the given record. Input processing is applied to the values in the following ways:

• If the field is a Date, a Time, or a DateTime value, its value is automatically wrapped in %Datein, %TimeIn, or %DateTimeIn, respectively.

• If the field is a string, its value is automatically wrapped in quotes.

• If the field has a null value, NULL is the given value.

Parameters

Example

Suppose that the record &REC has one key: FNUM, and the FCHAR field has changed. Here is an example:

Local record &REC; 
 
&REC = CreateRecord(RECORD.MYRECORD); 
&REC.FNUM.Value = 27; 
&REC.FCHAR.Value = 'Y'; 
SQLExec("Update TABLE set %UpdatePairs(:1) where %KeyEqual(:1)", &REC)

The example expands to:

"Update TABLE set FCHAR = 'Y' where FNUM = 27"

The following example updates all the fields on a base record (&REC) that are not also fields on the related language record (&REC_RELATED_LANG). It creates a holding record (&REC_TEMP), copies the fields to update from the base record to the holding record, and then uses the holding record for the update.

&UPDATE = CreateSQL("Update %Table(:1) set %UpdatePairs(:1) Where %KeyEqual(:2)"); 
&REC_TEMP = CreateRecord(@("RECORD." | &REC.Name)); 
&FIELD_LIST_ARRAY = CreateArray(); 
For &I = 1 to &REC_RELATED_LANG.FieldCount 
   &FIELD_LIST_ARRAY.Push(&REC_RELATED_LANG.GetField(&I).Name); 
End-For; 
 
For &I = 1 to &REC.FieldCount 
   If &FIELD_LIST_ARRAY.Find(&REC.GetField(&I).Name) = 0 then 
      &REC_TEMP.GetField(&I).Value = &REC.GetField(&I).Value; 
   End-If; 
End-For; 

&UPDATE.Execute(&REC_TEMP, &REC);

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.

UPDATE STATISTICS tablename

UPDATE STATISTICS tablename WITH FULLSCAN

execute DBMS_STATS.GATHER_TABLE_STATS (ownname=> 'PT861GA', tabname=>'PSSTATUS', ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,NO_INVALIDATE=>FALSE, CASCADE=>TRUE)

RUNSTATS ON TABLE tablename AND INDEXES ALL

RUNSTATS ON TABLE tablename WITH DISTRIBUTION AND DETAILED INDEXES ALL

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

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

%DeleteStats(table_name)

%UpdateStats(tablename)

Syntax

%Upper(charstring)

Description

The %Upper construct converts the string charstring to uppercase. You can use wildcards with charstring, such as the percent sign (%).

CALL 'PTPUPPER' USING SQLRT

			<any PIC S9(4) COMP field that contains the fields 
defined length (non-unicode)>

			<the String field - max PIC X(8192).>

01  W-WORK.

   02   W-DESCR      PIC X(90)   VALUE SPACES.
	   02   W-SIZE       PIC S9(4)   COMP VALUE +30.
      CALL 'PTPUPPER' USING SQLRT
         W-SIZE OF W-WORK
         W-DESCR OF W-WORK

Parameters

Example

SELECT EMPLID, NAME FROM PS_EMPLOYEES WHERE %UPPER(NAME) LIKE %UPPER(sch%)