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
lightbulbRefer 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.
  1. Double-click the input field of [Key column] of the Property Inspector.


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


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


  6. Press [OK] button.
  7. 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 EA 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

Notes

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
KEY1VALUE1
SundaySun
MondayMon
TuesdayTue
WednesdayWed
ThursdayThu
FridayFri
SaturdaySat
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
KEY1VALUE1
SundaySun
MondayMon
TuesdayTue
WednesdayWed
ThursdayThu
FridayFri
SaturdaySat
Result Data
3/20 (Sun)
3/21 (Mon)
3/22 (Tue)
3/23 (Wed)
3/24 (Thu)
3/24 (Fri)
3/25 (Sat)