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.
When defining filters in the ELT Map editor, note that strings are case sensitive in Oracle database.
- 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
- 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
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.