Meta-SQL Elements

This chapter provides an overview of meta-SQL and discusses:

See Also

SQLExec

ScrollSelect

Record Class

Rowset Class

SQL Class

Click to jump to parent topicUnderstanding Meta-SQL

This section discusses:

Click to jump to top of pageClick to jump to parent topicMeta-SQL Use

Meta-SQL expands to platform-specific SQL substrings, causes another function to be called, or substitutes a value. Meta-SQL constructs are used in functions that pass SQL strings, such as the following:

Click to jump to top of pageClick to jump to parent topicMeta-SQL Element Types

There are three types of meta-SQL elements:

Click to jump to parent topicParameter Markers

Parameter markers or bind variables are most commonly used in predicates, however some database platforms allow them in the SELECT list. However, since this is not supported across all platforms, you should not code your SQL to use bind variables in a SELECT list.

In addition, do not have bind variables as the operands of the same operator. This is not supported on all platforms. DB2/400 and DB2/OS390 cannot handle this type of operation.

Click to jump to parent topicDate Considerations

This section discusses:

Click to jump to top of pageClick to jump to parent topicBasic Date Meta-SQL Guidelines

You can avoid confusion when using meta-SQL such as %Datein and %Dateout if you remember to use "in" functions in the Where subclause of a SQL query and to use "out" functions in the Select (main) clause of the query. For example:

select emplid, %dateout(effdt) from ps_car_alloc a where car_id = '" |⇒ &REGISTRATION_NO | "' and plan_type = '" | &PLAN_TYPE | "' and a.effdt = ⇒ (select max (b.effdt) from ps_car_alloc b where a.emplid=b.emplid and b.effdt <=⇒ %currentdatein) and start_dt <= %currentdatein and (end_dt is null or end_dt >=⇒ %currentdatein)";

Click to jump to top of pageClick to jump to parent topicDate, DateTime, and Time Wrappers with Application Engine Programs

Use date or time wrappers (%Datein, %TimeOut, and so on) when selecting date or time columns into memory. Different database platforms use different internal formats for these data types. Those different formats range from 1900-01-01 to 01-JAN-1900. DateTime (timestamp) formats are even more complex.

In PeopleCode (SQLExecs and the like), use both an "out" wrapper when selecting a DateTime value into memory, as well as an "in" wrapper when referencing the value as a bind variable.

In an Application Engine program, when you populate a DateTime state field in a %Select, you still must use an "out" wrapper to get the value into the standard format. But when you reference this state field in a %Bind, Application Engine automatically provides the "in" wrapper around the substituted literal or bind marker (the latter if reuse is in effect).

Actually, if you use the code %Bind(date) in the select list of another %Select statement, to load the value into another date field, Application Engine doesn't provide a wrapper (since you are selecting a value that is already in the standard format, you do not need to use a wrapper).

Click to jump to top of pageClick to jump to parent topicDate, DateTime, and Time Out Wrappers for SQL Views and Dynamic Views

Dynamic views containing Date, Time, or DateTime fields must be wrapped with the appropriate meta-SQL. PeopleTools uses the SQL directly from the view definition (view text) and doesn't generate anything, so no meta-SQL wrapping is done.

SQL views should not contain meta-SQL that wraps Date, Time, or DateTime fields.

Click to jump to top of pageClick to jump to parent topic{DateTimein-prefix} in SQR

In SQR, if you are using {DateTimein-prefix}, and so on, you need to do the following:

Click to jump to parent topicMeta-SQL Placement Considerations

Not all meta-SQL can be used by all programs. Some meta-SQL can be used only in Application Engine programs. Other meta-SQL can only be used as part of a SQL statement in a SQL view or dynamic view. The following table lists available meta-SQL elements and where each element can be used.

If a meta-SQL construct, function, or meta-variable is supported in PeopleCode, it is supported in all types of PeopleCode programs—that is, in Application Engine PeopleCode programs (actions), component interface PeopleCode programs, and so on.

Note. Even if a meta-SQL element is used in PeopleCode, you cannot use meta-SQL like a built-in function. You can use meta-SQL in the SQLExec function, the Select method, the Fill method, and so on.

Note. Meta-SQL is not available in SQR.

Meta-SQL elements that are available for Application Engine only are described in Application Engine documentation.

Meta-SQL Element Name

Used in All Types of
PeopleCode Programs

Used in Application Engine
SQL Actions

Used in COBOL

Used in Dynamic Views
and SQL Views

%Abs

X

X

 

X

%AEProgram

 

X

   

%AESection

 

X

   

%AEStep

 

X

   

%AsOfDate

 

X

   

%AsOfDateOvr

 

X

   

%BINARYSORT

X

X

X

X

%Bind

 

X

   

%Cast

 X

X

 X

%ClearCursor

 

X

   

%COALESCE

X

X

 

X

%Comma

 

X

   

%Concat

X

X

 

X

%CurrentDateIn

X

X

X

X

%CurrentDateOut

X

X

X

X

%CurrentDateTimeIn

X

X

X

X

%CurrentDateTimeOut

X

X

X

X

%CurrentTimeIn

X

X

X

X

%CurrentTimeOut

X

X

X

X

%DateAdd

X

X

X

X

%DatabaseRelease

X

X

X

X

%DateDiff

X

X

X

X

%DateIn

X

X

X

X

%DateNull

X

X

 

X

%DateOut

X

X

X

X

%DatePart

X

X

 

X

%DateTimeDiff

X

X

X

X

%DateTimeIn

X

X

X

X

%DateTimeNull

X

X

 

X

%DateTimeOut

X

X

X

X

%DecDiv

X

X

X

X

%DecMult

X

X

X

X

%Delete

X

     

%DTTM

X

X

 

X

%EffDtCheck

X

X

   

%Execute

 

X

   

%ExecuteEdits

 

X

   

%FirstRows

X

X

   

%GetProgText

 

X

   

%Insert

X

     

%InsertSelect

X

X

 

X

%InsertSelectWithLongs

X

X

 

X

%InsertValues

X

   

X

%JobInstance

 

X

   

%Join

X

X

 

X

%KeyEqual

X

   

X

%KeyEqualNoEffDt

X

   

X

%LeftParen

 

X

   

%Like

X

X

 

X

%LikeExact

X

X

 

X

%List

 

X

   

%ListBind

 

X

   

%ListEqual

 

X

   

%Mod

X

X

 

X

%Next and %Previous

 

X

   

%NoUppercase

X

X

 

X

%NumToChar

X

X

 

X

%OldKeyEqual

X

   

X

%OPRCLAUSE

     

X

%ProcessInstance

 

X

   

%ResolveMetaSQL

 

X

   

%ReturnCode

 

X

   

%RightParen

 

X

   

%Round

X

X

X

X

%RoundCurrency

 

X

   

%RunControl

 

X

   

%Select

 

X

   

%SelectAll

X

     

%SelectByKey

X

     

%SelectByKeyEffDt

X

     

%SelectDistinct

X

     

%SelectInit

 

X

   

%Space

 

X

   

%SQL

X

X

 

X

%SQLRows

 

X

   

%Substring

X

X

X

X

%SUBREC

     

X

%Table

X

X

 

X

%Test

X

X

   

%TextIn

Note. %TextIn is not supported on Informix.

X

X

 

X

%TimeAdd

X

X

   

%TimeIn

X

X

X

X

%TimeNull

X

X

 

X

%TimeOut

X

X

X

X

%TimePart

X

X

 

X

%TrimSubstr

X

X

X

X

%Truncate

X

X

X

X

%TruncateTable

X

X

X

X

%Update

X

     

%UpdatePairs

X

   

X

%UpdateStats

 

X

 

%Upper

X

X

 

X

%UuidGen

X

X

   

%UuidGenBase64

X

X

   

See Also

Using Meta-SQL and PeopleCode

Click to jump to parent topicMeta-SQL Reference

This section discusses meta-SQL elements in alphabetical order.

Note. The parameter recname refers to a record name, not a table name. If you specify a table name (for example, PS_ST_OPTION_PARMS) you receive a SQL error. Use the record name (for example, ST_OPTION_PARMS) instead. Also, do not use quotation marks around a record name.

See Also

PeopleCode Typographical Conventions.

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

Syntax

%Abs(x)

Description

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

Note. This meta-SQL construct is not implemented for COBOL.

Example

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

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

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.

Note. Using %BINARYSORT in Where and Order By clauses negates the use of any indexes, as most databases can't use indexes for functional comparisons. (For example, WHERE %BINARYSORT(column) > 'X'). Use this syntax only when sorting equivalence of SQL statement results and COBOL memory order is required.

Parameters

Recname

Specify the record name to use with the sorting.

Example

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

See Also

RemoteCall.

Running COBOL in a Unicode Environment

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

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.

Note. %NumToChar will preserve all trailing zeroes. Therefore, use the scale parameter of %Cast to specify the number of trailing zeroes.

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

source_expr

Specify the input expression in the form of a Number, Long Character, Date, Time, or DateTime column name or as a %CurrentDateOut, %CurrentTimeOut, or %CurrentDateTimeOut meta-SQL variable.

This parameter is not case sensitive.

source_type

Specify the source data type. Valid data types are Number, Long, Date, Time, and DateTime.

This parameter is not case sensitive.

target_type

Currently the only target type supported is Character.

precision.scale

The precision.scale parameter is currently supported on DB2 UDB for z/OS only and with a source type of Number. While this parameter can be supplied on other platforms, it is ignored.

This parameter is optional.

The scale parameter is an optional part of this parameter. Therefore, the expression precision.0 is equivalent to precision.

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

Syntax

%COALESCE(expr1, expr2, ...)

Description

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

Note. This meta-SQL function is not implemented for COBOL.

Parameters

expr1. . .exprn

Specify the expressions to check.

Note. You cannot specify bind parameters using these expressions.

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;

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

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 Sybase it's replaced with a +.

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.

Note. Concat is not available in COBOL, but the DYN-STMT-CONCAT field can be strung into dynamic COBOL strings to resolve into a platform-specific concatenation operator.

Example

SELECT 'A' %Concat 'B' FROM PS_INSTALLATION. . . SELECT LAST_NAME %Concat ',' %Concat FIRST_NAME FROM PS_EMPLOYEE

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

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.

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

Description

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

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

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.

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

Description

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

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

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.

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

Description

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

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

Syntax

%DatabaseRelease([descr_level])

Description

The %DatabaseRelease variable returns the database version of the current database connection. The return value is a number or a string depending on descr_level.

Optionally specify the description level as MAJOR, FULL, or DESCR.

If MAJOR is specified, %DatabaseRelease returns the major release number as a number value.

If FULL is specified, %DatabaseRelease returns the full release and version as a string value.

If DESCR is specified, %DatabaseRelease returns the full release and version with description as a string value.

Parameters

descr_level

Specify the level of description to be returned. Valid values are MAJOR, FULL, and DESCR.

This parameter is optional. The default is MAJOR.

Example

If the current database is Oracle Database 10g Enterprise Edition Release 10.2.0.3.0- 64bit Production With the Partitioning and Data Mining option:

SQLExec("Select %DatabaseRelease(MAJOR) from PS_INSTALLATION_TR", &DBRel);

Returns 10.

SQLExec("Select %DatabaseRelease(FULL) from PS_INSTALLATION_TR", &DBRel);

Returns '10.2.0.3.0',

SQLExec("Select %DatabaseRelease(DESCR) from PS_INSTALLATION_TR", &DBRel);

Returns 'Oracle Database 10g Enterprise Edition Release 10.2.0.3.0- 64bit Production With the Partitioning and Data Mining options'.

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

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 PS_INSTALLATION_TR",⇒ &add); WinMessage(&add);

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

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.

Example

%DateDiff(%DateIn('1997-01-01'), %DateIn("1966-06-30')) %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('1997-01-01', '1996-06-30') (should use %DateIn for inputting date⇒ literals)

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

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.

Restrictions Using COBOL

You can only use string literals when using this construct in COBOL. You cannot use it with bind parameters in COBOL. For example, the following works in COBOL:

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

The following SQL fails:

UPDATE PS_PERSONAL_DATA SET LASTUPDT = %DATEIN(:1)

Parameters

dt

Specify either a Date value or a date literal in YYYY-MM-DD format.

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

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.

Note. This meta-SQL variable is not implemented for COBOL.

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

Database

Resolved Substring

Informix

empty string ('')

DB2

NULLIF(CURRENT DATE, CURRENT DATE)

All others

NULL

Parameters

None.

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

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

dt

Specify dt as a date column.

Note. You cannot specify a literal value for dt. Code such as %DateOut('1900-01-01') is not allowed.

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

Syntax

%DatePart(DTTM_Column)

Description

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

Note. This meta-SQL variable is not implemented for COBOL.

Considerations using %DatePart

Use %DateOut meta-SQL when fetching values, as in the following example:

%DateOut(%DatePart(DTTM_COLUMN)) from some_table

If a literal is used as the parameter to %DatePart, it must be wrapped in %DateTimeIn:

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

Parameters

DTTM_Column

Specify the datetime column from which you want to return the date.

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

Syntax

%DateTimeDiff(datetime_from, datetime_to)

Description

The %DateTimeDiff meta-SQL function returns a time value, representing the difference between two datetimes 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;

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

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.

Restrictions Using COBOL

You can only use string literals when using this construct in COBOL. You cannot use it with bind parameters in COBOL. For example, the following works in COBOL:

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

The following SQL fails:

UPDATE PS_PERSONAL_DATA SET LASTUPDTTM = %DATETIMEIN(:1)

Parameters

dtt

Specify either a DateTime bind variable or a string literal in the form YYYY-MM-DD-hh.mm.ss.ssssss.

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

Syntax

%DateTimeNull

Description

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

Note. This meta-SQL is not implemented for COBOL.

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

Database

Resolved Substring

Informix

empty string ('')

DB2

NULLIF(CURRENT TIMESTAMP, CURRENT TIMESTAMP)

All others

NULL

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

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

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

datetime_col

Specify a datetime column.

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

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

a

Specify the dividend as a number.

b

Specify the divisor as a number.

Example

%DecDiv(1000.0, :1)

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

See Also

%Mod

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

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

Note. %DecMult is replaced with a simple multiplication function on all platforms except for the DB2 UDB for OS/390 and z/OS platform. On this platform, it is converted to MULTIPLY_ALT. The MULTIPLY_ALT scalar function returns the product of the two arguments as a decimal value. It is provided as an alternative to the multiplication operator, especially when the sum of the precisions of the arguments exceeds 31.

Note. If you receive an overflow error using this meta-SQL, you may need to use the CAST function on the MSSQL, ORACLE, DB2UNIX and DB2 UDB for OS/390 platforms, or the CONVERT function for SYBASE platforms, on your input first.

Parameters

a

Specify a number to be multiplied.

b

Specify a number to use for multiplying.

Example

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

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

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

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.

Note. This meta-SQL function is not implemented for COBOL.

Example

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

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

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.

Note. This meta-SQL construct is not implemented for COBOL.

%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

recordname

Specify the record name to use as the record in the effective-date checking. This can be a bind variable, a record object, or a record name in the form recname. You cannot specify a RECORD. recname, a record name in quotation marks, or a table name.

Note. If you specify a bind variable, it should refer to a record object, not a string variable.

correlation_id1

(Optional) Specify the letter used inside the effective-dating subselect. If this parameter isn't specified, recordname is used.

correlation_id2

Specify the letter already assigned to the main record in the From clause of the SQL statement.

as_of_date

Specify the date to use in the effective date. This can be a bind variable, a variable, or a hard-coded date. 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.

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'

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

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:

Considerations Using %FirstRows

Consider the following when using %FirstRows:

Parameters

n

Specify the number of rows to optimize retrieval for.

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

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

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.

Note. %InsertSelect has a limit of 99 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.

Note. Because of the way long values (LongChar and Image fields) are handled in the various database platforms for Insert statements, all long values in insert_recname are skipped in the generated Insert statement. This implies that these fields should be defined in such a manner as to allow null values.

If you need to include long values in insert_recname use %InsertSelectWithLongs.

See %InsertSelectWithLongs.

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.

Note. You cannot use bind variables with the override_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.

Note. This meta-SQL is not implemented for COBOL.

Parameters

DISTINCT

Specify if the Select statement being generated should contain a Distinct clause.

insert_recname

Specify the name of record being inserted into. You must specify a record name, not RECORD. recname, a record name in quotation marks, a bind variable, or a table name.

Note. If the record for insert_recname is a temporary table, %InsertSelect automatically substitutes the corresponding table instance (PS_TARGETnn instead of PS_TARGET).

select_recname

Specify the name of record being selected from. You can specify more than one record. You must specify a record name, not a RECORD. recname, a record name in quotation marks, or a table name.

correlation_id

Identify the correlation ID to be used for the select_recname records and fields.

override_field

Specify the name of a field on insert_recname that you want to supply a value for (instead of using the value supplied from the select_recname.)

Value

Specify the value that should be used for the override_field instead of the value from select_recname.

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

See Also

%InsertSelectWithLongs.

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

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

Note. %InsertSelectWithLongs has a limit of 99 override 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.

Note. You cannot use bind variables with the override_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.

Note. This meta-SQL is not implemented for COBOL.

Parameters

DISTINCT

Specify if the Select statement being generated should contain a Distinct clause.

insert_recname

Specify the name of record being inserted into. You must specify a record name, not RECORD. recname, a record name in quotation marks, a bind variable, or a table name.

Note. If the record for insert_recname is a temporary table, %InsertSelectWithLongs automatically substitutes the corresponding table instance (PS_TARGETnn instead of PS_TARGET).

select_recname

Specify the name of record being selected from. You can specify more than one record. You must specify a record name, not a RECORD. recname, a record name in quotation marks, or a table name.

correlation_id

Identify the correlation ID to be used for the select_recname records and fields.

override_field

Specify the name of a field on insert_recname that you want to supply a value for (instead of using the value supplied from the select_recname.)

Value

Specify the value that should be used for the override_field instead of the value from select_recname.

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

See Also

%InsertSelect.

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

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:

Note. This meta-SQL construct can only be used in PeopleCode programs, not in Application Engine SQL actions. Also, this meta-SQL construct is not implemented for COBOL.

Parameters

recname

Specify the name of the record to be used for inserting. This can be a bind variable, a record object, or a record name in the form recname. You can't specify a RECORD. recname, a record name in quotation marks, or a table name.

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

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

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.

Note. This meta-SQL construct is not implemented for COBOL. If date key fields are not marked as required in the record definition for either of the referenced tables in the %Join clause, a Null clause check is added to the date field comparison. This additional clause can have a significant impact on the execution time for the generated SQL statement.

Parameters

{COMMON_KEYS | COMMON_FIELDS}

Use COMMON_KEYS to specify that all common primary key fields are used in constructing a Where clause; use COMMON_FIELDS to specify all common fields, not just key fields. You can select either COMMON_KEYS or COMMON_FIELDS.

join_recname

Specify the name of the record to be joined. This can be a bind variable, a record object, or a record name in the form recname. You can't specify a RECORD. recname, a record name in quotation marks, or a table name.

correlation_id1

Identify the correlation ID used to relate the record specified by join_recname and its fields.

to_recname

Specify the name of the record to be joined to. This can be a bind variable, a record object, or a record name in the form recname. You can't specify a RECORD. recname, a record name in quotation marks, or a table name.

correlation_id2

Identify the correlation ID used to relate the record specified by to_recname and its fields.

override_field_list

Specify a list of fields that you do not want used in the join. For example, if fields A, B, and C were common to two records, and you didn't want to join on C, list C as an override_field.

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'

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

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:

Note. This meta-SQL can only be used in PeopleCode programs, not in Application Engine SQL actions. Also, this meta-SQL is not implemented for COBOL.

Parameters

recname

Specify the name of the record to use for inserting. 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 recname and its fields.

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"

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

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:

Note. This meta-SQL can only be used in PeopleCode programs, not in Application Engine SQL actions. Also, this meta-SQL is not implemented for COBOL.

Parameters

recname

Specify the name of the record to be used for inserting. This can be a bind variable, a record object, or a record name in the form recname. You can't 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 recname and its fields.

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"

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

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.

Note. This meta-SQL is not implemented for COBOL.

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.

Using %Like and Eliminating Blanks

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:

Using %Like and Trailing Blanks

Not all executions of %Like perform the same. When dealing with trailing blanks, some platforms behave as if there is an implicit percent sign (%) at the end of the comparison string, while most do not.

In the following example, if the selected column contains the string "ABCD " (with three trailing blanks. The statement may or may not return any rows:

select * from t1 Where c like 'ABCD'

Therefore, it is always important to explicitly code the percent sign (%) the end of matching strings for columns where you want to include trailing blanks. The following table shows the use of implicit percent signs with specific databases:

Database

Includes Implicit Percent Sign (%)

PeopleSoft Standard Usage

Yes

DB2/400

No

DB2/MVS

No

DB2/Unix

No

Informix

No

Microsoft SQL Server

Yes

Oracle

No

SQLBase

No

Sybase

Yes

Using %Like and Wildcards

SQL specifies two wildcards that can be used when specifying pattern matching strings for use with the SQL Like predicate. The underscore is used as a substitution for a single character within a string, and the percent sign represents any number of character spaces within a string. All supported databases use these characters as wildcards.

Parameters

literal

Specify the value to search for.

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

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.

Note. This meta-SQL is not implemented for COBOL.

%LikeExact generates one of the following:

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:

Considerations Using Bind Markers

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

The following forms work:

This form does not work:

SQLExec("UPDATE PS_AE_APPL_TMP SET AE_PRODUCT = 'X' WHERE %LIKEEXACT(AE_APPL_ID, :⇒ 1)", "AB_C");

See Also

ExpandSqlBinds.

Parameters

fieldname

Specify a field to be used in the first part of the Like comparison.

literal

Specify the value to search for.

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

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

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.

Parameters

a

Specifies the dividend as a number.

b

Specifies the divisor as a number.

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

See Also

%DecDiv

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

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

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

Syntax

%NumToChar(Number)

Description

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

Note. %NumToChar will preserve all trailing zeroes. Therefore, use the scale parameter of %Cast to specify the number of trailing zeroes.

Parameters

Number

Specify the number to convert to a character value. Signed numbers, as well as decimals, are acceptable.

See Also

%Cast.

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

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.

Note. This meta-SQL construct can only be used in PeopleCode programs, not in Application Engine SQL actions. Also, this meta-SQL is not implemented for COBOL.

See Also

%KeyEqual.

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

Description

The %OPRCLAUSE metavariable is used in the view text of dynamic views. In PeopleTools 6, the %OPRCLAUSE metavariable expanded in the following manner:

SELECT EMPLID, ABSENCE_TYPE, ​oprid ​FROM PS_ABSENCE_HIST WHERE %OPRCLAUSE SELECT EMPLID, ABSENCE_TYPE, OPRID FROM PS_ABSENCE_HIST WHERE ( ​OPRCLASS ​='⇒ HRADMIN') AND (EMPLID='8001' AND ABSENCE_TYPE='CNF') ORDER BY EMPLID, ABSENCE_TYPE

In PeopleTools 7, to support the new concept of a specific row-level security class, this metavariable now fills in the Where clause with the value from PSOPRDEFN.ROWSECCLASS.

%OPRCLAUSE must be either all uppercase or all lowercase.

%OPRCLAUSE translates to OprId or OprClass, following the same rules used for security on search dialog boxes. If OPRID is in the view, %OPRCLAUSE expands to OPRID = 'current operator'. If OPCLASS is in the view, %OPRCLAUSE expands to OPCLASS = 'current class'.

Example

Here is an example:

SELECT EMPLID, ABSENCE_TYPE, OPRID FROM PS_ABSENCE_HIST WHERE %OPRCLAUSE AND ⇒ (EMPLID='8001' AND ABSENCE_TYPE='CNF')

This code expands to:

SELECT EMPLID, ABSENCE_TYPE, OPRID FROM PS_ABSENCE_HIST WHERE ( OPRID =⇒ 'PTDMO') AND (EMPLID='8001' AND ABSENCE_TYPE='CNF') ORDER BY EMPLID, ABSENCE_TYPE

Here's another example:

SELECT EMPLID, ABSENCE_TYPE, OPRCLASS FROM PS_ABSENCE_HIST WHERE %OPRCLAUSE AND ⇒ (EMPLID='8001' AND ABSENCE_TYPE='CNF')

This code expands to:

SELECT EMPLID, ABSENCE_TYPE, OPRID FROM PS_ABSENCE_HIST WHERE ( OPRCLASS =⇒ 'ALLPANLS') AND (EMPLID='8001' AND ABSENCE_TYPE='CNF') ORDER BY EMPLID, ABSENCE_⇒ TYPE

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

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

expression

Specify an arbitrary numeric expression involving numeric constants and database columns.

factor

Specify an integer or bind variable in SQLExec PeopleCode. The range of a factor is from -31 to +31 for literals. Non-literals can only be positive.

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)

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

Syntax

%SQL(SQLid [, 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. SQLid is the name of a SQL definition created using either Application Designer or the StoreSQL function.

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

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

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

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

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

If no version is found, an error occurs.

See Using the SQL Editor.

Application Engine Considerations

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

Special SQL Characters

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

Meta-Variable

Description

%Comma

Represents a single comma.

%LeftParen

Allows you to pass a left parenthesis character to a %P() variable, without closing the SQL object.

%RightParen

Allows you to pass a right parenthesis character to a %P() variable, without closing the SQL object.

%Space

Represents a space.

Parameters

SQLid

Specify the name of an existing SQL definition.

paramlist

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

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

Example

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

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

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

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

The previous example resolves to the following:

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

See Also

SQL Class

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

Syntax

%Substring(source_str, start, length)

Description

%Substring expands to a substring of source_str.

Note. For the DB2 LUW database, you must ensure that the source_str parameter doesn't resolve to an expression greater than 1000 characters.

Parameters

source_str

Specify the source string.

start

Specify the substring's beginning position. The first character of source_str is position 1.

length

Specify the length of the substring.

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

Syntax

%SUBREC(subrec_name, corel_name)

Description

%SUBREC is used only in dynamic view SQL, where it expands to the columns of a subrecord. You can't use this statement in SQLExec or any other SQL statement.

Note. %SUBREC must be either all uppercase or all lowercase.

Parameters

subrec_name

Specify the name of the subrecord.

corel_name

Specify the correlation name.

Example

Suppose you have a record definition AAA_VW that is a dynamic view, with fields CHR, SUB, and NUM. The field SUB is a subrecord with fields CHR_SUB, NUM_SUB, and IMG_SUB. The view text for AAA_VW could be:

"select a.chr, %subrec(sub,a), a.num from ps_aaa a"

The Create View SQL generated by this view text would be:

"CREATE VIEW SYSADM.PS_AAA_VW (CHR, CHR_SUB, NUM_SUB, IMG_SUB, NUM) AS SELECT⇒ A.CHR, A.CHR_SUB, A.NUM_SUB, A.IMG_SUB, A.NUM FROM PS_AAA A"

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

Syntax

%Table(recname [, instance])

Description

The %Table construct returns the SQL table name for the record specified with recname.

For example, %Table(ABSENCE_HIST) returns PS_ABSENCE_HIST.

Note. This meta-SQL is not implemented for COBOL.

If the record is a temporary table and the current process has a temporary table instance number assigned, %Table resolves to that instance of the temporary table (that is, PS_ABSENCE_HISTInstance Number).

You can override this value with the instance parameter. For example, if you know you want the third instance of a temporary table, you could specify it with %Table(&MYREC, 3). You can use the SetTempTableInstance function to set the instance of a temporary table that is used with %Table.

This construct can be used to specify temporary tables for running parallel Application Engine processes.

See Also

%Table.

Parameters

recname

Identify the record that the table name is 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.

instance

Specify the instance of the temporary table to be used.

Example

The following function deletes records based on two other fields:

Function delete_draft_type(&RECNAME) &SQL = "Delete from %Table(:1) where " | FIELD.SETID | " = :2 and " | FIELD.DRAFT_TYPE | " = :3"; SQLExec(&SQL, @("RECORD." | &RECNAME), SETID, DRAFT_TYPE); End-Function;

See Also

SetTempTableInstance.

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

Syntax

%Test(Prefix, Test, Suffix)

Description

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

Parameters

Prefix

Specify a string that is conditionally added before the expansion of the test string. You cannot use meta-SQL in this parameter.

Test

Specify a meta-SQL string to be expanded.

Suffix

Specify a string that is conditionally added at the end of the test string. You can use meta-SQL in this parameter.

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

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

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. If you do not use this meta-SQL wrapper, this type of operation fails on Sybase.

Important! %TextIn is not supported on Informix. In addition, this meta-SQL construct is not implemented for COBOL.

Parameters

BindVariable

Specify a bind variable.

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)

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

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

Note. On some platforms, you can use time-value in place of datetime. However, this can give a SQL error on other platforms (for example, Informix) if the result of the %TimeAdd construct would result in a new date (for example, 11:59PM + 2 minutes). This meta-SQL construct is not implemented for COBOL.

Parameters

time

Specify a Time or DateTime value to add more time to.

add-minutes

Specify the number of minutes to add to time. This must be a numeric value or an expression that resolves to a numeric value.

Example

SELECT %TimeAdd(%CurrentTimeIn, 60) FROM PS_INSTALLATION

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

Syntax

%TimeIn(tm)

Description

%TimeIn expands to platform-specific SQL for a Time value in the Where clause of a SQL Select or Update statement, or when a time value is passed in an Insert statement.

Restrictions Using COBOL

You can only use string literals when using this construct in COBOL. You cannot use it with bind parameters in COBOL. For example, the following works in COBOL:

UPDATE PS_PERSONAL_DATA SET LASTUPTM = %TIMEIN('11:59:00:000000')

The following SQL fails:

UPDATE PS_PERSONAL_DATA SET LASTUPTM = %TIMEIN(:1)

Parameters

tm

Specify a Time bind variable or a string literal in the form hh.mm.ss.ssssss.

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

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.

Note. This meta-SQL is not implemented for COBOL.

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

Database

Resolved Substring

Informix

empty string ('')

DB2

NULLIF(CURRENT TIME, CURRENT TIME)

All others

NULL

Parameters

None.

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

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

time_col

Specify a time column.

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

Syntax

%TimePart(DTTM_Column)

Description

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

Note. This meta-SQL is not implemented for COBOL.

Considerations Using %TimePart

Use %TimeOut meta-SQL when fetching from the database:

%TimeOut(%TimePart(DTTM_COLUMN)) from some_table

If a literal is used as the parameter to %TimePart, it must be wrapped in %DateTimeIn,as shown in the following:

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

Parameters

DTTM_Column

Specify the datetime column to return the time for.

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

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.

Note. If you trim a string of blanks, an empty string is returned on all database platforms except Oracle, when a Null is returned. If a Null result is not acceptable, such as when using the result as a value to insert into a non-nullable column, you can turn the Null into a single blank using the %COALESCE meta-SQL with %TrimSubstr, for example: %COALESCE( %TrimSubstr( <expression>), ' ')

Parameters

source_str

Specify the source string.

start

Specify the substring's beginning position. The first character of source_str is position 1.

length

Specify the length of the substring.

See Also

%Substring.

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

Syntax

%Truncate(expression, factor)

Description

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

Considerations Using %Truncate

You may get incorrect results or runtime SQL errors if you try to use an expression for factor. The following code example produces incorrect results:

%Truncate(10.337, 2 + 1)

Parameters

Expression

Specify an expression involving numeric constants and database columns.

Factor

Specify an integer or bind variable in SQLExec PeopleCode. The range of a factor is -30 to +31. A negative number truncates to left of the decimal point.

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.

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

Syntax

%TruncateTable(table_name)

Description

%TruncateTable deletes all the rows in a table.

Note. You must use a table name, not a record name, with this statement.

On all databases, the use of %TruncateTable causes an implicit commit. The rows deleted by this command, and any other pending database updates, are all committed. To postpone the commit until subsequent database updates have been successfully completed, use the SQL statement DELETE FROM table_name or the statement IMPORT REPLACE WITH NULL instead of %TruncateTable(table_name). The advantage of using %TruncateTable is that its execution is faster than either of the SQL statements. %TruncateTable is often used for removing rows from a work table or a temporary table.

If you're calling %TruncateTable from an Application Engine program step, you should commit after the step that immediately precedes the step containing the %TruncateTable statement. Also, do not use %TruncateTable on a step that is executed multiple times within a loop. In general, it's best to 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 CallAppEngine function.

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

For databases that either execute an implicit commit for %TruncateTable or require a commit before or after this meta-SQL, replace %TruncateTable with an unconditional delete in certain circumstances.

See %TruncateTable.

Example

If you use %TruncateTable with %Table, you must specify the full name of the table. For example:

%TruncateTable(%Table(BAS_ELIG_DBGFLD))

The following is a code example:

%TruncateTable(PS_TEMP_TABLE)

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

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:

Note. This meta-SQL construct can only be used in PeopleCode programs, not in Application Engine SQL actions. Also, this meta-SQL construct is not implemented for COBOL.

Parameters

recname

Specify the name of the record to use for updating. This can be a bind variable, a record object, or a record name in the form recname. You can't 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 recname and its fields.

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

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

Syntax

%Upper(charstring)

Description

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

Note. This meta-SQL construct is not implemented for COBOL.

Considerations with COBOL and Unicode

COBOL's uppercase function is not Unicode-aware, and corrupts Unicode data. To use an uppercase function with COBOL, use the function supplied with PeopleTools called PTPUPPER.

The syntax to call PTPUPPER is:

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

The following is an example from Unicode-expanded source code:

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

charstring

Specify the string to convert to uppercase.

Example

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

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

Syntax

%UuidGen()

Description

Use the %UuidGen function in a SQL Insert or Update statement to generate a universally unique identifier (UUID) as a globally unique 36-character string.

%UuidGen can only be used in an Insert or Update statement. You will get an error if you use the function in any other type of SQL.

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

Syntax

%UuidGenBase64()

Description

Use the %UuidGenBase64 function in a SQL Insert or Update statement to generate a universally unique identifier (UUID) as a globally unique 24-character base64 string.

%UuidGenBase64 can only be used in an Insert or Update statement. You will get an error if you use the function in any other type of SQL.

Click to jump to parent topicMeta-SQL Shortcuts

Take advantage of the following shortcuts to use the entire list of key fields for a record.

Note. The meta-SQL shortcuts can only be used in PeopleCode programs, not in Application Engine SQL actions. Also, none of the meta-SQL shortcuts are implemented for COBOL.

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

Syntax

%Delete(:num)

Description

This is a shorthand for:

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

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

Syntax

%Insert(:num)

Description

This is a shorthand for:

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

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

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.

Using %SelectAll with CreateSQL

You can use %SelectAll with the CreateSQL function without a record object. It must subsequently be executed with the record object with which you want to do the Select statement. Here is an example:

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

You could also move the CreateRecord SQL statements out of the loop (and then move the close statements out of the loop too).

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

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.

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

Syntax

%SelectByKey(:num [ correlation_id ])

Description

This is a shorthand for:

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

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

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)

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

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.