Step 3: Creating and loading view into a final reporting table - 7.3

Change Data Capture

Version
7.3
Language
English
Product
Talend Big Data
Talend Big Data Platform
Talend Data Fabric
Talend Data Integration
Talend Data Management Platform
Talend Data Services Platform
Talend ESB
Talend MDM Platform
Talend Real-Time Big Data Platform
Module
Talend Studio
Content
Data Governance > Third-party systems > Database components (Integration) > Change Data Capture
Data Quality and Preparation > Third-party systems > Database components (Integration) > Change Data Capture
Design and Development > Third-party systems > Database components (Integration) > Change Data Capture
Last publication date
2024-02-21
In this step, a view created as on the top of the base table employee and the external table employee_extnl, so that only the latest data is shown. This view would need to be then loaded into a final reporting table which will be accessed by the subscribers of the data.

Procedure

Use the following SQL:
CREATE VIEW employee_view AS

SELECT t1.* FROM

 

(SELECT * FROM employee

    UNION ALL

    SELECT * FROM employee_extnl) t1

JOIN

    (SELECT employeeid, max(record_datetime) max_modified FROM

        (SELECT * FROM employee

        UNION ALL

        SELECT * FROM employee_extnl) t2

    GROUP BY employeeid) s

ON t1.employeeid = s.employeeid AND t1.record_datetime = s.max_modified;

The results from the view are as follow:

The view generates only the latest data.