As described in Aggregating table columns and filtering, configure a Job for data aggregation using the corresponding ELT components for
Oracle database - tELTOracleInput,
tELTOracleMap, and tELTOracleOutput.
Execute the Job to save the aggregation result in a database table named
Agg_Result.
Note:
When defining filters in the ELT Map editor, note that strings are
case sensitive in Oracle database.
Procedure
-
Launch the ELT Map editor and add a new output table named
update_data.
-
Add a filter row to the update_data table to set up a
relationship between input and output tables:
owners.ID_OWNER =
agg_result.ID_OWNER.
-
Drop the MAKE column from the cars table to the
update_data table.
-
Drop the NAME_RESELLER column from the resellers table
to the update_data table.
-
Add a model enclosed in single quotation marks, 'A8' in
this use case, to the MAKE column from the cars table,
preceded by a double pipe.
-
Add Sold by enclosed in single quotation marks in front
of the NAME_RESELLER column from the resellers table, with a double pipe in
between.
-
Check the Generated SQL select query tab
to be executed.
-
Click OK to validate the changes in the
ELT Mapper.
-
Deactivate the tELTOracleOutput
component labeled Agg_Result by right-clicking it and
selecting Deactivate Agg_Result from the
contextual menu.
-
Drop a new tELTOracleOutput component
from the Palette to the design workspace,
and label it Update_Data to better identify its
functionality.
-
Connect the tELTOracleMap component to
the new tELTOracleOutput component using
the link corresponding to the new output table defined in the ELT Mapper,
update_data in this use case.
-
Double-click the new tELTOracleOutput
component to display its Basic settings
view.
-
From the Action on data list, select
Update.
-
Check the schema, and click Sync columns
to retrieve the schema structure from the preceding component if
necessary.
-
In the WHERE clauses area, add a clause
that reads
agg_result.MAKE = 'Audi' to update
data relating to the make of Audi in the database table
agg_result.
-
Fill the Default Table Name field with
the name of the output link, update_data in this use
case.
-
Select the Use different table name
check box, and fill the Table name field
with the name of the database table to be updated,
agg_result in this use case. Leave the other
parameters as they are.
