REPLACE_DB function

Function

REPLACE_DB{CODE,DBCID,TABLE_NAME,SELECT_ITEM,WHERE_ITEM[,NOMATCHVALUE]}

Issues an SQL statement (SELECT statement) by using the condition specified as an argument, and returns the search results from the database table. The SQL statement (SELECT statement) generated using the arguments is as follows:

SELECT SELECT_ITEM FROM TABLE_NAME WHERE WHERE_ITEM = input-data

If multiple values matched based on the WHERE condition, the first matched record is returned as the result. Furthermore, database connectivity information specified in argument DBCID and the encoding specified in CODE are used to connect to the database.

Parameters

CODE

Specify the encoding. Only one of the following encoding types can be used: EUC, SJIS, UTF-8, UTF-16LE, UTF-16BE, or UTF-16. (This parameter cannot be omitted.)

DBCID

Specify the database connection ID. (This parameter cannot be omitted.)

TABLE_NAME

Specify the table name. (This parameter cannot be omitted.)

SELECT_ITEM

Specify the field name for the value you want to import. (This parameter cannot be omitted.)

If an item that does not exist is entered, the function results in an error when executed.

WHERE_ITEM

Specify the item name that is the condition for importing a value. (This parameter cannot be omitted.)

Binary (type I) fields cannot be used.

If an item that does not exist is entered, the function results in an error when executed.

NOMATCHVALUE

Specify the value to be used if a value cannot be imported. (This parameter can be omitted.)

If you omit this parameter, the function results in an error upon execution if a value cannot be imported.

Note

Based on the database specifications, input data types that cannot be used in the WHERE clause are as follows:

Table A.10 Input data types that cannot be used for the WHERE clause
DB	TYPE
DB2	BLOB
	CLOB
	LONG VARCHAR
	DBCLOB
	GRAPHIC
	LONG VARGRAPHIC
	VARGRAPHIC
Oracle	LONG
	LONG RAW
	CLOB
	NCLOB
	BFILE
	BLOB
SQL Server	IMAGE
	TEXT
	NTEXT
	XML
PostgreSQL	bytea

The functionality of the REPLACE_DB function allows for similar processing by utilizing the matching function. However, it is recommended that the matching function is used in cases that do not meet the following conditions.

Conditions in which the REPLACE_DB function is recommended:

The input data encoding is an EBCDIC type.

The number of records in the database table referenced is large (on the order of several million records).

Examples of use

Figure A.26 Example of using the REPLACE_DB function