using meta-SQL 使用元SQL

%DatePart

Syntax

%DatePart(DTTM_Column)

Description

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

DatePart meta-SQL变量返回指定的DateTime列的日期部分。

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

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

Considerations using %DatePart

使用%DatePart的注意事项

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

获取值时使用%DateOut meta-SQL，如以下示例所示:

%DateOut(%DatePart(DTTM_COLUMN)) from some_table

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

如果文本用作%DatePart的参数，则必须将其包装在%DateTimeIn中:

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

Parameters

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

%DateTimeDiff

Syntax

%DateTimeDiff(datetime_from, datetime_to)

Description

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

DateTimeDiff meta-SQL函数返回一个时间值，表示两个日期时间之间的差异（以分钟为单位）。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;

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

对于SQL Select或Update语句的Where子句中的DateTime值，或者当在Insert语句中传递DateTime值时，%DateTime In meta-SQL变量将扩展为特定于平台的SQL。

Restrictions Using COBOL

使用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:

在COBOL中使用此构造时，只能使用字符串文字。您不能将它与COBOL中的绑定参数一起使用。例如，以下代码在COBOL中运行:

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

The following SQL fails:

以下SQL失败:

UPDATE PS_PERSONAL_DATA SET LASTUPDTTM = %DATETIMEIN(:1)

Parameters

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

%DateTimeNull

Syntax

%DateTimeNull

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

使用%DateTimeNull meta-SQL变量为DateTime字段指定空值。仅在插入或更新子句中使用此元SQL。不要在Where子句中使用这个元SQL。

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

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

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

Database	Resolved Substring
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_CURR⇒ ENCY = %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_A⇒ K_SBR) ,STATISTICS_CODE, FISCAL_YEAR,ACCOUNTING_PERIOD

%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

DateTimeOut元SQL变量根据数据库平台展开为特定于平台的SQL子字符串或datetimevalue，表示SQL查询的Select子句中的datetime列

Parameters

Parameter	Description
datetime_col	Specify a datetime column.

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

DecDiv meta-SQL函数返回一个数字，表示a除以b的值，其中a和b是数值表达式。

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

如果结果需要由BIND变量获取，则使用字符类型或PIC X(50)来获取结果。

Parameters

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

在示例中，：1是SQLExec PeopleCode中的绑定变量。

Related Links

%Mod

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

%DecMult meta-SQL函数返回一个表示a乘以b的数字，其中a和b是数字表达式。

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

如果结果需要由BIND变量获取，则使用字符类型或PIC X(50)来获取结果。

Note: %DecMult is replaced with a simple multiplication function on all platforms except for the DB2 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 precision of the arguments exceeds 31.

附注：在除DB2 forOS/390和z/OS平台之外的所有平台上，%DecMult都被替换为一个简单的乘法函数。在这个平台上，它被转换为MULTI_ALT。乘法_ALT标量函数将两个参数的乘积作为十进制值返回。它是作为乘法运算符的替代运算符提供的，尤其是当参数的精度之和超过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 for OS/390 platforms.

注意：如果使用此元SQL收到溢出错误，则可能需要在MSSQL、ORACLE、DB2UNIX和DB2 for OS/390平台上使用CAST函数。

Parameters

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

在示例中，c1、c2和c3是Number数据类型的字段。

%Delete

Syntax

%Delete(:num)

Description

This is a shorthand for:

这是一个简写：

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

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

DTTM meta-SQL函数将日期值中的数据库日期与时间值中的数据馆时间组合起来，并返回一个数据库时间戳值。

Note: For Microsoft SQL Server and DB2 databases, do not use null characters for the time argument. You can use default values such as 00.00.00.000000.

附注：对于微软SQL Server和DB2数据库，不要使用空字符作为time参数。可以使用默认值，如0.00.000.000。

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

附注：这个元SQL函数不是为COBOL实现的。

Example

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

Related Links

%DateIn

%TimeAdd

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

EffDtCheck构造扩展为适用于Where子句的有效日期子查询。as_of_date的值自动包装在%DateIn中，除非as_of_date已经包装在%DateIn中或引用其他数据库列。

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

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

%EffDtCheck仅适用于有效日期。它不考虑有效序列号（EF SEQ）。它也不执行有效状态（EFF_STATUS）检查。

Parameters

Parameter	Description
recordname	Specify the record name to use as the record in the effectivedate 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.

在下面的示例中，&Date的值为01/02/1998。示例&Rec对象有一个EF DT键字段。

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:

下面的示例使用相关ID:

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'

%Execute

Syntax

%Execute([/])command1{; |

/}command2{; | /}...commandN{; | /}

Description

Use the %Execute function to execute database-specific commands from within your Application Engine program. Also, the %Execute function enables you to include multiple statements in a single Application Engine action without encountering database-specific differences. For instance, in some instances you could code a single Application Engine action to contain multiple SQL statements, and they might run successfully on one database platform. However, if you attempt to run the same code against a different database platform, you might encounter errors or skipped SQL.

使用%Execute函数从应用程序引擎程序中执行特定于数据库的命令。此外，%Execute函数使您可以在单个应用程序引擎操作中包含多个语句，而不会遇到特定于数据库的差异。例如，在某些情况下，您可以编写包含多个SQL语句的单个应用程序引擎操作，并且它们可能在一个数据库平台上成功运行。但是，如果尝试在不同的数据库平台上运行相同的代码，则可能会遇到错误或跳过SQL。

By default, Application Engine expects a semicolon to be used to delimit multiple commands within an %Execute function statement. You can instruct Application Engine to use a forward slash (/) delimiter instead by placing a forward slash inside the function parentheses.

默认情况下，应用程序引擎要求在%Execute函数语句中使用分号分隔多个命令。通过在函数括号内放置正斜杠，可以指示应用程序引擎使用正斜杠（/）分隔符。

Note: When you use the %Execute function, it must be located at the beginning of the statement and must be the only function or command contained in the statement. The action type must be SQL.

注意：当您使用%Execute函数时，它必须位于语句的开头，并且必须是语句中包含的唯一函数或命令。操作类型必须是SQL。

Note: No comment statement should be used in a SQL code when using %Execute.

注意：当使用%Execute时，SQL代码中不应使用注释语句。

Example

The following code enables you to use an Oracle PL/SQL block in an %Execute statement:

下面的代码使您能够在%Execute语句中使用甲骨文PL/SQL块：

%Execute(/)

DECLARE

 counter INTEGER;

BEGIN

 FOR counter := 1 TO 10

  UPDATE pslock SET version = version + 1;

 END FOR;

END;

/

%ExecuteEdits

Syntax

%ExecuteEdits(type,recordname [alias][,field1,field2, ...])

Description

Use the %ExecuteEdits function to apply data dictionary edits in batch. The %ExecuteEdits function is Application Engine-only meta-SQL. You cannot use it in COBOL, SQR, or PeopleCode, not even in Application Engine PeopleCode.

使用%ExecuteEdits函数批量应用数据字典编辑。ExecuteEdits函数是仅适用于应用程序工程的元SQL。您不能在COBOL、SQR或PeopleCode中使用它，甚至不能在应用程序引擎PeopleCode中使用它。

Notes About %ExecuteEdits

注释关于%ExecuteEdits

Note the following points about the %ExecuteEdits function:

关于%ExecuteEdits函数，请注意以下几点:

• Consider performance carefully when using this function.
• 使用此功能时要仔细考虑性能。

Prompt table and Translate table edits have a significant effect because they involve correlated subqueries. Run a SQL trace at runtime so that you can view the SQL generated by %ExecuteEdits. Look for opportunities to optimize it.

提示表和翻译表编辑具有显著的效果，因为它们涉及到相关的子查询。在运行时运行SQL跟踪，以便可以查看%ExecuteEdits生成的SQL。寻找机会对其进行优化。

• In general, %ExecuteEdits is best used on a temporary table.
• 一般来说，%ExecuteEdits最好用在临时表上。

If you must run it against a real application table, you should provide Where clause conditions to limit the number of rows to include only those that the program is currently processing. Process the rows in the current set at one time rather than row by row.

如果必须对真实的应用程序表运行它，则应提供Where子句条件来限制行数，使其仅包括程序当前正在处理的行数。一次处理当前集中的行，而不是一行一行地处理。

• With %ExecuteEdits, you cannot use work records in a batch, set-based operation.
• 使用%ExecuteEdits，您不能在基于批处理的基于集合的操作中使用工作记录。

All higher-order key fields used by prompt table edits must exist in the record that your code intends to edit, and the field names must match exactly. For example,

提示表编辑所使用的所有较高顺序的关键字字段必须存在于代码要编辑的记录中，并且字段名必须完全匹配。举个例子，

%ExecuteEdits(%Edit_PromptTable, MY_DATA_TMP)

The record MY_DATA_TMP contains the field STATE with a prompt table edit against

PS_REGION_VW, which has key fields COUNTRY and REGION. The REGION field corresponds to STATE, and COUNTRY is the higher-order key. For %ExecuteEdits to work correctly, the MY_DATA_TMP record must contain a field called COUNTRY. The edited field (STATE) can use a different name because Application Engine always references the last key field (ignoring EFFDT).

记录MY_DATA_TMP包含字段STATE和一个针对PS_REGION_VW的提示表编辑，该表具有关键字段COUNTRY和REGION。REGION字段对应于STATE，COUNTRY是高阶键。要使%ExecuteEdits正确工作，MY_DATA_TMP记录必须包含一个名为COUNTRY的字段。编辑的字段（STATE）可以使用不同的名称，因为应用程序引擎始终引用最后一个键字段（忽略EFFDT）。

• In Application Engine, %ExecuteEdits uses the system date when performing comparisons with effective date (EFFDT); however, in some cases this date is not appropriate (Journal Edit, Journal Import, and so on). In these situations, use Journal Date when comparing with EFFDT. To override the use of the default system date with a selected field from a joined table, use %AsOfDateOvr. For example,
• 在应用程序引擎中，%ExecuteEdits在执行与生效日期（EFFDT）的比较时使用系统日期；但是，在某些情况下，此日期不合适（日记编辑、日记导入等）。在这些情况下，使用日记日期与EF DT进行比较。覆盖使用默认系统日期和联接表中的选定字段，请使用%AsOfDateOvr。举个例子，

%ExecuteEdits(%AsOfDateOvr(alias.fieldname), %Bind(...)...)

• Restrict the number and type of edits to the minimum required.
• 将编辑的数量和类型限制在所需的最小值。

Do not edit fields that are known to be valid or that are given default values later in the process. Also consider using a separate record with edits defined specifically for batch or provide a list of fields to be edited.

不要编辑已知有效的字段或稍后在流程中提供默认值的字段。还可以考虑使用专门为批处理定义的编辑的单独记录，或提供要编辑的字段列表。

Parameters

Parameter	Description
type	Specify any combination of the following (added together): %Edit_Required %Edit_YesNo %Edit_DateRange %Edit_PromptTable %Edit_TranslateTable
recordname	Specify the record used to obtain the data dictionary edits.
field1, field2, ...	Specify a subset of the fields of the record to which edits apply.

Example

Suppose you want to insert rows with missing or invalid values in three specific fields, selecting data from a temporary table but using edits defined on the original application table. Notice the use of an alias, or correlation name, inside the meta-SQL:

假定您想要在三个特定字段中插入包含缺失值或无效值的行，从临时表中选择数据，但使用在原始应用程序表上定义的编辑。注意meta-SQL中使用了别名或相关名称:

INSERT INTO PS_JRNL_LINE_ERROR (...)

SELECT ... FROM PS_JRNL_LINE_TMP A

WHERE A.PROCESS_INSTANCE = %BIND(PROCESS_INSTANCE)   AND %EXECUTEEDITS(%Edit_Required + %Edit_PromptTable,?

 JRNL_LINE A, BUSINESS_UNIT, JOURNAL_ID, ACCOUNTING_DT)

To update rows that have some kind of edit error in a temporary table, you can use custom edits defined in the temporary table record:

要更新临时表中存在某种编辑错误的行，可以使用临时表记录中定义的自定义编辑:

UPDATE PS_PENDITEM_TAO

SELECT ERROR_FLAG = 'Y'

WHERE PROCESS_INSTANCE = %BIND(PROCESS_INSTANCE)   AND %EXECUTEEDITS(%Edit_Required + %Edit_YesNo + %Edit_DateRange +?

 %Edit_PromptTable + %Edit_TranslateTable, PENDITEM_TAO)