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

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.

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!

Leave your feedback here