|
Note: This column mapping type is available in ETL module version 1.1.300 and newer. |
ETL Library makes few assumptions about the existence of tables or columns. Without any overrides, by default all tables are optional (do not have to exist at all). In case of columns, the optional state looks a bit different though:
•All columns not specifically named, but referenced via the usage of mapRemaining attribute are optional.
•Named columns must exist.
It is possible to define a fallback for every column that references a single column name. Fallback is another expression, which is taken if the original source column does not exist.
To define a fallback, set-up the fallback property for each affected column, where the value of fallback follows the same syntax as a typical column definition (minus column type and fallback). For example, to define that a column Name should be taken, and if it does not exist a value of "n/a" is expected, use the following:
"UserName": {
"column": "Name",
"fallback": {
"value": "n/a"
}
}
Fallback is supported for the following column mappings:
Tips:
•You can use a fixed value with null target value to denote that a null value must be placed for a non-existing column.
•You can use a typical mapping syntax to use value from another column
•It is not possible to define a different type for fallback column. Fallback always uses the type from the column it belongs to.
•Additionally, it is not possible to nest a fallback in another fallback.
Example
Given the following table Table1:
Name |
Country |
Age |
|---|---|---|
Helmut |
DE |
20 |
Marcin |
PL |
40 |
With the following JSON file
{
"id": 1,
"type": "map",
"name": "Fallback Demo",
"source": "Table1",
"columns": {
"Name": "Name",
"City": {
"column": "City",
"fallback": {
"value": "n/a"
}
},
"BornYear": {
"column": "Year",
"fallback": {
"sql": "2021 - Age"
}
}
},
"target": "Table2"
}
The following Table2 is produced:
Name |
City |
BornYear |
|---|---|---|
Helmut |
n/a |
2001 |
Marcin |
n/a |
1981 |