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 – please let us know!
