using meta-SQL 使用元SQL

%LeftParen

Description

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

使用%LeftParen元变量指定左括号。用法类似于% Comma。

Related Links

%Comma

“%SQL” (PeopleCode Language Reference)

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

Like结构会扩展以查找文字值。在寻找相似的值时，应该使用这个元SQL。百分号字符（%）追加到文字后面。

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

注意：这个元SQL不是为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.

如果在SQLExec中使用绑定标记（如“：1”）作为文本参数，则必须使用ExpandSqlBinds函数包装SQL字符串。ExpandSqlBinds将绑定标记替换为实际输入值。

%Like generates the following:

%Like生成以下内容:

like 'literal%'

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

如果文字值包含反斜杠字符（\）或百分号（%），则%Like生成以下内容：

like 'literal%' escape '\'

See ExpandSqlBinds.

Using %Like and Eliminating Blanks

使用%like并消除空格

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:

有些平台要求您使用RTRIM来获得正确的值。以下字符是通配符，即使前面有反斜杠（\）转义字符:

• %
• _

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

因此，在某些平台上，文字必须以百分号（%）结束，并且不能以反斜杠（\）开头。下面是一些例子：

• literal = 'ABC%'

There is no need for RTRIM on any platform.

• literal = 'ABC\%'

You need RTRIM on Microsoft SQL Server and DB2.

Using %Like and Trailing Blanks

使用%Like和尾随空白

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.

并非%Like的所有执行都执行相同的操作。在处理尾随空格时，一些平台的行为就好像在比较字符串的末尾有一个隐式百分号（%），而大多数平台没有。

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:

在下面的示例中，如果所选列包含字符串“ABCD”（后面有三个空格）。语句可以返回也可以不返回任何行：

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
Microsoft SQL Server	Yes
Oracle	No
SQLBase	No

Using %Like and Wildcards

使用%Like和通配符

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.

SQL指定了两个通配符，可在指定与-SQL Like谓词一起使用的模式匹配字符串时使用。下划线用于替换字符串中的单个字符，百分号表示字符串中的任意数量的字符空间。所有受支持的数据库都使用这些字符作为通配符。

Parameters

Parameter	Description
literal	Specify the value to search for. 指定要搜索的值。

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

LikeExact元SQL变量展开以查找文本值。在需要精确匹配时使用此变量，同时考虑到文字值中的Account通配符。

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

注意：这个元SQL不是为COBOL实现的。

%LikeExact generates one of the following:

%LikeExact生成以下内容之一:

• If the literal contains no wildcards:
• 如果文字不包含通配符：
• fieldname = 'literal'
• If the literal ends with the '%' wildcard:
• 如果文本以“%”通配符结尾：
• fieldname like 'literal' [escape '\']

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

有些平台要求您使用RTRIM来获得正确的值。以下字符是通配符，即使前面有反斜杠（\）转义字符。

• %
• _

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

因此，在某些平台上，文字必须以百分号（%）结束，并且不能以反斜杠（\）开头。下面是一些例子：

• literal = 'ABC%'

You do not need RTRIM on any platform.

在任何平台上都不需要RTRIM。

• literal = 'ABC\%'

You need RTRIM on Microsoft SQL Server and DB2.

字面意思=“ ABC ”\%

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.

如果在SQLExec中使用绑定标记（如“：1”）作为文本参数，则必须使用ExpandSqlBinds包装SQL字符串。ExpandSqlBinds将绑定标记替换为实际输入值。

The following forms work:

以下表格可以使用:

• Application Engine SQL action (with or without the ReUse property enabled).
• 应用程序引擎SQL操作（启用或不启用ReUse属性）。

UPDATE PS_AE_APPL_TMP SET AE_PRODUCT = 'X' WHERE %LIKEEXACT(AE_APPL_ID, %Bind(⇒ AE_APPL_ID, STATIC))

The STATIC modifier is only required if the ReUse property is enabled, but you can always use it.

STATIC修饰符仅在启用ReUse属性时才需要，但您始终可以使用它。

• PeopleCode.

AE_TESTAPPL_AET.AE_APPL_ID = "AB\_C";

SQLExec("UPDATE PS_AE_APPL_TMP SET AE_PRODUCT = 'X' WHERE %LIKEEXACT(AE_APPL_I⇒

D, :AE_TESTAPPL_AET.AE_APPL_ID)");

Here is another acceptable form:

下面是另一种可以接受的形式：

SQLExec(ExpandSqlBinds("UPDATE PS_AE_APPL_TMP SET AE_PRODUCT = 'X' WHERE %Like⇒

Exact(AE_APPL_ID, :1)", "AB\_C"));

This form does not work:

这种形式不起作用：

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

)", "AB\_C");

Parameters

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

Related Links

ExpandSqlBinds

%List

Syntax

%List({FIELD_LIST

| FIELD_LIST_NOLONGS | KEY_FIELDS | ORDER_BY},recordname [correlation_id])

Description

The %List construct expands into a list of field names delimited by commas. The fields included in the expanded list depend on the parameters.

List构造扩展为一个由逗号分隔的字段名列表。扩展列表中包含的字段取决于参数。

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

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

Considerations for Using %List

使用%List的注意事项

When using %List in an Insert/Select or Insert/Values or %Select statement, you must have matching pairs of %List (or %ListBind) variables in the target and source field lists. Use the same list type argument and record name to ensure consistency.

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

Parameters

Parameter	Description
FIELD_LIST	Use all field names in the given record. You can select only one option from FIELD_LIST, ORDER_BY, FIELD_LIST_ NOLONGS, or KEY_FIELDS.
KEY_FIELDS	Use all key fields in the given record. You can select only one option from FIELD_LIST, FIELD_LIST_NOLONGS, KEY_ FIELDS, or ORDER_BY.
ORDER_BY	Use all the key fields of recordname, adding the DESC field for descending key columns. This parameter is often used when the list being generated is for an Order By clause. You can select only one option from FIELD_LIST, KEY_FIELDS,  ORDER_BY, or FIELD_LIST_NOLONGS.
FIELD_LIST_NOLONGS	Use all field names in the given record, except any long columns (long text or image fields.) You can select only one option from FIELD_LIST, ORDER_BY, KEY_FIELDS, or FIELD_LIST_NOLONGS.
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.
correlation_id	Identify the single-letter correlation ID to relate the record specified by recordname and its fields.

Example

The following is a good example of using %List. Both the Insert and Select statements use the same %List variable:

下面是使用%List的一个很好的例子。Insert和Select语句都使用相同的%List变量：

INSERT INTO PS_PO_DISTRIB_STG (  %Sql(POCOMMONDISTSTGFLDLSTU)

,  %List(FIELD_LIST, CF16_AN_SBR)

,  MERCHANDISE_AMT

,  MERCH_AMT_BSE

,  QTY_DEMAND

,  QTY_PO

,  QTY_PO_STD

,  QTY_REQ)

SELECT  %Sql(POCOMMONDISTSTGFLDLSTU)

,  %List(FIELD_LIST, CF16_AN_SBR)

,  MERCHANDISE_AMT

,  MERCH_AMT_BSE

,  QTY_DEMAND

,  QTY_PO

,  QTY_PO_STD

,  QTY_REQ

FROM PS_PO_DIST_STG_WRK WRK

WHERE WRK.PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE)

The following example shows a poor example of how to use %List. The Insert and Select field lists both use %List, but the Select field list is only partly dynamic; the rest is hard-coded.

下面的示例显示了如何使用%List的糟糕示例。Insert和Select字段列表都使用%List，但是Select字段列表只有一部分是动态的；其余部分是硬编码的。

INSERT INTO PS_EN_TRN_CMP_TMP (%List(FIELD_LIST, EN_TRN_CMP_TMP))

SELECT B.EIP_CTL_ID

, %List(SELECT_LIST, EN_BOM_COMPS A)

, E.COPY_DIRECTION

, E.BUSINESS_UNIT_TO

, E.BOM_TRANSFER_STAT

, 'N'

, B.MASS_MAINT_CODE

, 0

 FROM PS_EN_BOM_COMPS A

 , PS_EN_ASSY_TRN_TMP B

 , PS_EN_TRNS_TMP E WHERE ...

The following example shows the previous poor example rewritten in a better way:

下面的例子显示了以更好的方式重写的以前的糟糕的例子：

INSERT INTO PS_EN_TRN_CMP_TMP (EIP_CTL_ID,

,  %List(FIELD_LIST, EN_BOM_COMPS)

,  COPY_DIRECTION

,  BUSINESS_UNIT_TO

,  BOM_TRANSFER_STAT

,  EN_MMC_UPDATE_FLG

,  MASS_MAINT_CODE

,  EN_MMC_SEQ_FLG01 ,  ...

, EN_MMC_SEQ_FLG20)

SELECT B.EIP_CTL_ID

  , %List(FIELD_LIST, EN_BOM_COMPS A)

, E.COPY_DIRECTION

, E.BUSINESS_UNIT_TO

, E.BOM_TRANSFER_STAT

, 'N'

, B.MASS_MAINT_CODE

, 0 , ...

, 0

 FROM PS_EN_BOM_COMPS A

 , PS_EN_ASSY_TRN_TMP B

 , PS_EN_TRNS_TMP E WHERE ...

The following code segment is another poor example. Only the field list of the Insert statement is dynamically generated, and the Select statement is statically coded. If the table STL_NET_TBL is reordered, the Insert statement will be incorrect.

下面的代码段是另一个糟糕的例子。只有Insert语句的字段列表是动态生成的，Select语句是静态编码的。如果STL_NET_TBL表被重新排序，则Insert语句将是不正确的。

INSERT INTO PS_STL_NET_TBL (%List(FIELD_LIST, STL_NET_TBL ) )

SELECT :1

, :2

, :3

, :4

, :5

, :6

, :7

,:8

FROM PS_INSTALLATION

The following code shows the previous poor example rewritten in a better way:

下面的代码显示了以更好的方式重写的前一个糟糕的例子：

INSERT INTO PS_STL_NET_TBL (%List(FIELD_LIST, STL_NET_TBL))

VALUES (%List(BIND_LIST, STL_NET_TBL MY_AET))