using meta-sql 使用元sql 四

%ListBind

Syntax

%ListBind({FIELD_LIST | FIELD_LIST_NOLONGS | KEY_FIELDS},recordname [State_record_a⇒ lias])

Description

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

ListBind meta-SQL构造将字段列表扩展为绑定引用，以便在Insert/Value语句中使用。

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

附注：这个元SQL不是为COBOL、动态视图SQL或PeopleCode实现的。

Considerations for Using %ListBind

使用%ListBind的注意事项

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

当在insert/select或insert/values或%Select语句中使用%ListBind时，必须在目标和源字段列表中有匹配的%List或%ListBind对，并使用相同的列表类型参数和记录名称以确保一致性。

Parameters

Parameter	Description
FIELD_LIST	Use all field names in a record. You can select only one option from FIELD_LIST, FIELD_LIST_NOLONGS, or KEY_ FIELDS.
FIELD_LIST_NOLONGS	Use all field names in a record, except any long columns (long text or image fields). You can select only one option from FIELD_LIST, FIELD_LIST_NOLONGS, or KEY_FIELDS.
KEY_FIELDS	Use all key field names in a record. You can select only one option from FIELD_LIST, FIELD_LIST_NOLONGS, or KEY _FIELDS.
recordname	Identify either a record or a subrecord that the field names are drawn from. This can be a bind variable, a record object,  or a record name in the form recname. You cannot specify RECORD.recname, a record name in quotation marks, or a table name.
State_record_alias	Specify the Application Engine state record buffer that contains the values (this could be different than the record used to derive the field list). If missing, the default state record is assumed.

Example

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

T), %Bind(MY_AET.FIELDN))

%ListEqual

Syntax

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

Description

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

ListEqual构造映射每个字段，可能映射到具有%Bind值的别名，并在每个相等之前添加分隔符。每个字段映射如下:

alias.X = %Bind(recbuffer.X)

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

此结构可用于Update语句的Set子句或Where子句中。

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

附注：这个元SQL不是为COBOL、动态视图SQL或PeopleCode实现的。

Parameters

Parameter	Description
ALL | KEY	Specify if you want all fields or just key fields.
recordname	Identify either a record or a subrecord that the field names are drawn from. This can be a bind variable, a record object,  or a record name in the form recname. You cannot specify RECORD.recname, a record name in quotation marks, or a table name.
alias	(Optional) Specify an alias to precede each field name.
RecordBuffer	Specify the record buffer for the bind variables (this could be different than the record used to derive the field list).
Separator	If you want to specify a logical separator, specify either AND or OR with this parameter. If you do not specify a separator, no logical separator is used; the value of a comma is used instead.

Example

UPDATE PS_TEMP

SET %ListEqual(ALL, CF_SUBREC, MY_AET)

WHERE %ListEqual(KEYS, TEMP, MY_AET, AND)

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

使用%Mod meta-SQL函数返回一个数除以另一个数的余数（或模）。%mod使用被除数和除数的整数部分。如果除数为0，%Mod返回被除数值。

Example

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

以下每个示例都显示了%Mod函数的计算结果:

%Mod(10, 3) = 1

%Mod(9, 3) = 0

%Mod(10.1, 3) = 1

%Mod(-10, 3) = -1

%Mod(10, 0)= 10

Related Links

%DecDiv

%Next and %Previous

Description

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

使用%Next和%Previous函数按编号顺序返回下一个或上一个字段的值。这些函数在任何应用程序引擎SQL操作中都有效，并应在执行序列编号处理时使用。通常，您使用它们而不是%Bind构造。这些函数使用数字字段的当前值作为绑定变量，然后在语句成功运行后递增（%Next）或递减（%Previous）该值。数字字段指示状态记录的数字字段，该字段最初设置为特定值（如从1开始）。

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.

如果语句是“选择”，且不返回任何行，则字段值不会更改。替换规则与%Bind相同。例如，如果启用了ReUse属性，则该字段为真绑定（‘：n’替换）。否则，会发生内联替换。

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.

有了Do Select操作，递增/递减每次运行发生一次，而不是每次获取一次。因此，除非Do Select操作实现了Reselect属性，否则该值仅在循环的第一次迭代时更改。或者，使用“重新选择”属性或“同时做”和“做”在操作之前，每个迭代都将重新运行Select语句，然后获取一行。对于这些类型的循环，值在每次迭代时都会发生变化。

Related Links

%Bind

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

在处理SQL语句时，系统会自动将所有字段名和可能的记录名转换为大写。当处理来自第三方的记录时，小写的字段会转换为大写，这在区分大小写的平台上会产生运行时问题。

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

要防止这种情况，请在SQL语句的开头使用%NoUppercase元SQL语句。

Parameters

None.

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

注意，这个元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)

%NumToChar

Syntax

%NumToChar(Number)

Description

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

使用%NumToChar构造将数值转换为字符值。从Number中修剪空格。

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

注意：%NumToChar将保留所有尾随的零。因此，使用%Cast的比例参数来指定尾随零的数量。

Related Links

%Cast

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

OldKeyEqual meta-SQL构造类似于%KeyEqual构造，只是它使用记录字段的原始值，而不是当前值。由于哪些值是原始值，哪些是当前值的规则不是很清楚，特别是对于独立的记录对象，所以要避免使用这种meta-SQL结构。您应该使用单独的记录来保存以前的值。这样可以使你的代码更清晰，更易维护。

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.

附注：这个元SQL构造只能在PeopleCode程序中使用，不能在Application Engine SQL操作中使用。而且，这个元SQL不是为COBOL实现的。

Related Links

%KeyEqual

%ProcessInstance

Description

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

使用%ProcessInstance元变量指定数字（无引号）流程实例。

Note: It is recommended not to use %ProcessInstance inside a SQL step. %ProcessInstance is always replaced by the literal value before being sent to the database; whereas, %Bind(PROCESS_INSTANCE) is passed as a bind value. So, even though the ReUse flag for all of SQLs are set, Process Instance values are sent as referrals and that forces a parse each time the SQLs are executed.

附注：建议不要在SQL步骤中使用%ProcessInstance。%ProcessInstance在发送到数据库之前总是被文字值替换；而%Bind（PROCESS_INSTANCE）则作为绑定值传递。因此，即使为所有SQL设置了ReUse标志，Process Instance值仍作为引用发送，这将在每次执行SQL时强制进行解析。

%ResolveMetaSQL

Syntax

%ResolveMetaSQL(&SQL,%DbType)

Description

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

ResolveMetaSQL函数返回一个字符串，该字符串中的任何meta-SQL都扩展为平台特定的SQL，类似于在SQL编辑器中使用“解析元SQL”选项时在“元SQL”选项卡上返回的文本。

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

如果&SQL不包含任何元SQL，那么函数返回一个与&SQL相同的字符串。

%DBType value represents the type of current database.

%DBType值表示当前数据库的类型

Parameters

Parameter	Description
&SQL	Specify a string containing the SQL to be resolved.
%DBType	%DBType value is Db2 Also see “FetchSQL” (PeopleCode Language Reference).

Returns

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

具有元SQL的字符串扩展为特定于平台的SQL。

Example

Here is an example:

下面是一个例子：

&SQLText = FetchSQL(SQL.PTLT_CODE_MARKET);

&ResolveSQLText = ResolveMetaSQL(&SQLText,DB2);

Suppose &SQLText contains the following SQL:

假设&SQLText包含以下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):

相关链接“了解SQL编辑器窗口“（PeopleCode开发人员指南）

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)

Related Links

“Understanding the SQL Editor Window” (PeopleCode Developer’s Guide)

相关链接“了解SQL编辑器窗口”（PeopleCode开发人员指南）

%ReturnCode

Description

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

使用%ReturnCode元变量计算或指定上次执行的应用程序引擎程序步骤的返回代码。如果操作失败、中断或生成错误，%ReturnCode将设置为以下类型的返回代码之一:

• Database (SQL) call errors.
• 数据库（SQL）调用错误。
• PeopleCode function errors.
• PeopleCode函数的错误。
• GEN_ERROR, when produced by general runtime exceptions.
• GEN_ERROR，由一般运行时异常产生。
• AE_ABORT, when produced by application or runtime logic, including some memory–related errors.
• AE_ABORT，当由应用程序或运行时逻辑产生时，包括一些与内存相关的错误。

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

如果应用程序进程没有终止，那么对于每个后续成功的操作，%ReturnCode将重置为默认值0。

%RightParen

Description

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

使用%RightParen元变量指定右括号。用法类似于%逗号。

Related Links

%Comma %SQL

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

%将表达式舍入到小数点之前或之后的指定刻度。如果factor是文字，则可以四舍五入为负数。

Parameters

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

在本例中，field_c1和field_c2是记录中的两个字段。

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

以下情况是非法的，可能会导致不正确的结果或运行时SQL错误:

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

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

%RoundCurrency

Syntax

%RoundCurrency(expression, [ALIAS.]currency_field)

Description

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

使用%RoundCurrency函数返回四舍五入到货币精度的金额字段的值，精度由字段的货币控制字段属性指定，如“应用程序设计器记录字段属性”对话框中所定义。要使此函数工作，您必须在PeopleTools选项页上选择多币种选项。

See “Using Administration Utilities” (System and Server Administration).

参见“使用管理实用程序”(系统和服务器管理)。

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.

此函数是以前版本中出现的Application Engine&ROUND构造的增强版本，仅在Application Engine SQL中有效；对于SQLExecs或视图文本无效。

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.

可以在Update语句的Set子句或Insert/Select语句的Select列表中使用此函数。第一个参数是来自源表的数值和列的任意表达式，用于计算要舍入的货币金额。第二个参数是来自特定源表（Update表或Insert/Select语句的From子句中的表）的控制货币字段。此字段标识货币金额的相应货币值。

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

附注：请记住，应用程序引擎程序的截止日期用于获取货币舍入因子。货币舍入因子由PS_CURRENCY_CD_TBL中相应行中DECIMAL_POSITIONS的值确定，这是一个有效的日期表。

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

如果多币种不起作用，结果被四舍五入到金额字段的精度（13.2或15.3金额格式都是可能的）。

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)

%RunControl

Description

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

使用%RunControl元变量指定包含当前运行控制标识符的带引号字符串。当使用%RunControl时，无论AEREQUEST表是否包含行，运行控制ID都可用于程序。