[Merge Mapper]Replace by DB Table

Logic Name

Replace by DB Table

Function

Replaces the input string based on a DB table and returns the value of the string.

Data Types

Location	Type
The first input handler	String
Logic output	String

Refer to "Logic Specifications" for details.

Properties

Category	Property	Description
Required	Resource name	Specify the resource used to replace.
Required	Table name	Specify the name of the table used to replace.
Required	Key column	Specify the column to match with the value of the input handler.
Required	Value column	Specify the column to be used as replacement value.
Required	Matching method	Specify the matching method.
Required	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	The comment will also be output to the specifications.It does not 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 a part of the input handler 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: Replace by DB Table Logic - 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 of the multiple matching records.
Use last record	Replaces using the last of the multiple matching records. If the values of [Key column] of the matching records are different, use the last record which matches the [Key column] of the first record to replace.

Function Details

Specifying Multiple Keys

Multiple key columns can be specified with the following methods.

Double-click the input field of [Key column] of the Property Inspector.

Press "▼" button.
"Edit key column used to replace" dialog starts.

Press [Add] button and add [Key column Name].
Select column used as key from "▼" button with the input field of [Key column Name].

Press [OK] button.
The input handlers of Mapper logic is changed to the updated number of key columns.

Available Global Resources

Global resources of the following adapters can be specified in [Resource name].

Adapter Type
Access Adapter
DB2 Adapter
MySQL Adapter
Oracle Adapter
PostgreSQL Adapter
SQL Server Adapter
JDBC Adapter
ODBC Adapter
Dr.Sum Adapter
Amazon Aurora for MySQL Adapter
Amazon RDS for MySQL Adapter
Amazon RDS for Oracle Adapter
Amazon RDS for PostgreSQL Adapter
Amazon RDS for SQL Server Adapter
Azure SQL Database Adapter

Specification Limits

For [Table name], tables specified in the list of a global resource are displayed.

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 (eg: ${var}) is treated as simple string variable and will not be expanded.

Notes

If [Key column] or [Value column] is null, it is exempted 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.
If using a column which includes multi-byte characters or platform dependent characters for its name as [Key column] or [Value column], enclose the column name by quotation marks.
The type of quotation marks vary by databases. Please contact database vendors for more information.

Logic Sample

Mapping Definition

Logic Settings and Processing Result (Exact match)

Replaces the notations of weeks from alphabets to Kanji (Chinese characters) using the conversion 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	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

Result Data

Sun.

Mon.

Tue.

Wed.

Thu.

Fri.

Sat.

Logic Settings and Processing Result (Partial match)

Replaces the notations of weeks from Kanji (Chinese characters) to alphabets using the conversion 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	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

Result Data

3/20 (Sun)

3/21 (Mon)

3/22 (Tue)

3/23 (Wed)

3/24 (Thu)

3/24 (Fri)

3/25 (Sat)