Replace by DB Table

Logic name

Replace by DB Table (Document Mapper/Variable Mapper/Merge Mapper)

Function

This logic replaces the input string based on a DB table and returns the value of the string.

Data types

Location	Type
First input handler	String
Logic output	String

= Hint =

Refer to the following for details:

Logic specifications (Document Mapper/Variable Mapper)
Logic specifications (Merge Mapper)

Properties

Category	Property	Description
Required settings	Resource name	Specify the resource used to replace.
Required settings	Table name	Specify the name of the table.
Required settings	Key column	Specify the column to match against the value of the input handler.
Required settings	Value column	Specify the column to be used as replacement value.
Required settings	Matching method	Specify the matching method.
Required settings	Replacing method	Specify the replacing method.
Additional string	Prefix	Specify a string to add before the replacement string.
Additional string	Suffix	Specify a string to add after the replacement string.
0 match record	Processing option	Specify how to handle the case of 0 matching record.
0 match record	Alternative string	Specify the alternative string.
2 or more match records	Processing option	Specify how to handle the case of 2 or more match records.
Comment	Comment	You can write a description of the logic. It doesn't affect the conversion process.

Matching method

Option	Description
Exact match	Replaces when the value of the input handler matches the value of Key column exactly.
Partial match	Replaces when the value of the input handler partially matches the value of Key column.

Replacing method

Option	Description
Complete replacement	Replaces the value of the first input handler with the value of Value column.
Partial replacement	Replaces the matching part of the first input handler with the value of Value column.

0 match record/Processing option

Option	Description
Raise exception	Throws the following exception: [Mapping] process failed. Caused by:[com.appresso.ds.vp.mapper.fastconverter.NoSuchRecordException: ConvertByDBLogic - There is no such record that equals the following key (s): <Key column>]
Replace by alternative string	Replaces with the value of Alternative string.
Return value of first handler	Returns exactly the value of the first input handler.

Option

Description

Raise exception

Throws the following exception:

[Mapping] process failed. Caused by:[com.appresso.ds.vp.mapper.fastconverter.NoSuchRecordException: ConvertByDBLogic - There is no such record that equals the following key (s): <Key column>]

Replace by alternative string

Replaces with the value of Alternative string.

Return value of first handler

Returns exactly the value of the first input handler.

2 or more match records/Processing option

Option	Description
Raise exception	Throws the following exception: [Mapping] process failed. Caused by:[com.appresso.ds.vp.mapper.fastconverter.MultipleSuchRecordsException: ConvertByDB Logic - There are multiple such records that equal the following key (s): <Key Column>]
Use first record	Replaces using the first record of the multiple matching records.
Use last record	Replaces using the last record of the multiple matching records. Note If the values of Key column of the matching records are different, the last record which matches the value of Key column of the first record is used to replace.

Option

Description

Raise exception

Throws the following exception:

[Mapping] process failed. Caused by:[com.appresso.ds.vp.mapper.fastconverter.MultipleSuchRecordsException: ConvertByDB Logic - There are multiple such records that equal the following key (s): <Key Column>]

Use first record

Replaces using the first record of the multiple matching records.

Use last record

Replaces using the last record of the multiple matching records.

Note

If the values of Key column of the matching records are different, the last record which matches the value of Key column of the first record is used to replace.

Function details

Specifying multiple keys

Multiple key columns can be specified with the following steps:

Double-click the "Replace by DB Table" icon.
Click Add on the right side of Key column.
For the additional key column, select the key name to specify from the dropdown list.
Click Finish.
The input handlers of the Mapper logic are changed to the updated number of key columns.

Available connection resources

Connection resources for the following connectors can be specified in Resource name.

Connector type
DB2 Connector
MySQL Connector
Oracle Connector
PostgreSQL Connector
SQL Server Connector
JDBC Connector
Amazon Aurora for MySQL Connector
Amazon RDS for MySQL Connector
Amazon RDS for Oracle Connector
Amazon RDS for PostgreSQL Connector
Amazon RDS for SQL Server Connector
Azure SQL Database Connector

Specification limits

The number of tables displayed in Table name is determined in the settings for connection resources.
Only the first matching string will be replaced when the input value contains multiple strings to convert.
The script variable format contained in the converted string (such as ${var}) is treated as simple string variable and will not be expanded.

Notes

If Key column or Value column is null, it is excluded from replacing process.
- The number of the matching record is the same as the number of records in which Value column is other than null.
To use a column which includes multi-byte characters or platform-dependent characters in its name as Key column or Value column, enclose the column name by quotation marks.

= Remarks =
The type of quotation marks varies by database. Contact the database vendor for more information about definition of quotation marks.

Logic sample

Mapping definition

Logic settings and processing result (exact match)

This logic replaces the notations of weeks using the replacement table.

Property settings

Property	Setting
Resource name	DB
Table name	convertbydb_all
Key Column	KEY1
Value Column	VALUE1
Matching method	Exact match
Replacing method	Complete replacement
Prefix
Suffix	.
0 match record/Processing option	Return value of first handler
0 match record/Alternative string
2 or more match records/Processing option	Use first record

Input data

Sunday

Monday

Tuesday

Wednesday

Thursday

Friday

Saturday

Replacement table

KEY1	VALUE1
Sunday	Sun
Monday	Mon
Tuesday	Tue
Wednesday	Wed
Thursday	Thu
Friday	Fri
Saturday	Sat

Output data

Sun.

Mon.

Tue.

Wed.

Thu.

Fri.

Sat.

Logic settings and processing result (partial match)

This logic replaces the notations of weeks using the replacement table.

Property settings

Property	Setting
Resource name	DB
Table name	convertbydb_partial
Key column	KEY1
Value column	VALUE1
Matching method	Partial match
Replacing method	Partial replacement
Prefix
Suffix
0 match record/Processing option	Return value of first handler
0 match record/Alternative string
2 or more match records/Processing option	Use first record

Input data

3/20 (Sunday)

3/21 (Monday)

3/22 (Tuesday)

3/23 (Wednesday)

3/24 (Thursday)

3/24 (Friday)

3/25 (Saturday)

Replacement table

KEY1	VALUE1
Sunday	Sun
Monday	Mon
Tuesday	Tue
Wednesday	Wed
Thursday	Thu
Friday	Fri
Saturday	Sat

Output data

3/20 (Sun)

3/21 (Mon)

3/22 (Tue)

3/23 (Wed)

3/24 (Thu)

3/24 (Fri)

3/25 (Sat)

= Hint =

For a list of logics, refer to Mapper Logic Guide.