View your data
Embedded Files
Now run queries in the context of Time
Now run queries in the context of Time
EMP-DEPT
EMP-DEPT
We will use the EMP and DEPT tables, joined by business keys, to show a single query being traversed around the corrected and updated data to show different results at different times without the need for complicated sub queries on dates.
Note how the time context is set before the Select statement.
Note how the soe / eoe dates are projected from the generated code (not from columns in the data model).
Select
Select
set VALID_TIME(t1);
set TRANS_TIME();
select
e.name
,e.job
,m.name as manager_name
,e.sal
,e.comm
,d.name as dept_name
,d.location
,e.soe as hire_date
,e.eoe as terminate_date
from EMP e
join EMP m on m.id = e.mgr
join DEPT d on d.id = e.dpt_id
where e.id = 123;
<no rows>
Select
Select
set VALID_TIME(t2);
set TRANS_TIME();
select
e.name
,e.job
,m.name as manager_name
,e.sal
,e.comm
,d.name as dept_name
,d.location
,e.soe as hire_date
,e.eoe as terminate_date
from EMP e
join EMP m on m.id = e.mgr
join DEPT d on d.id = e.dpt_id
where e.id = 123;
<Fred, Technical Support, John, 130000, 0, IT, London, soe3, eoe2>
Select
Select
set VALD_TIME();
set TRANS_TIME();
select
e.name
,e.job
,m.name as manager_name
,e.sal
,e.comm
,d.name as dept_name
,d.location
,e.soe as hire_date
,e.eoe as terminate_date
from EMP e
join EMP m on m.id = e.mgr
join DEPT d on d.id = e.dpt_id
where e.id = 123;
<Fred, Sales Director, null, 130000, 0, Sales, Manchester, soe3, eoe2>
Select
Select
set VALID_TIME(t3);
set TRANS_TIME();
select
e.name
,e.job
,m.name as manager_name
,e.sal
,e.comm
,d.name as dept_name
,d.location
,e.soe as hire_date
,e.eoe as terminate_date
from EMP e
join EMP m on m.id = e.mgr
join DEPT d on d.id = e.dpt_id
where e.id = 123;
<Fred, Salesman, Luke, 100000, 2000, Sales, Manchester, soe3, eoe2>
Select
Select
set VALID_TIME(t4);
set TRANS_TIME();
select
e.name
,e.job
,m.name as manager_name
,e.sal
,e.comm
,d.name as dept_name
,d.location
,e.soe as hire_date
,e.eoe as terminate_date
from EMP e
join EMP m on m.id = e.mgr
join DEPT d on d.id = e.dpt_id
where e.id = 123;
<Fred, Sales Intern, Luke, 30000, 500, Sales, Manchester, soe3, eoe2>
Select
Select
set VALID_TIME(t6);
set TRANS_TIME();
select
e.name
,e.job
,m.name as manager_name
,e.sal
,e.comm
,d.name as dept_name
,d.location
,e.soe as hire_date
,e.eoe as terminate_date
from EMP e
join EMP m on m.id = e.mgr
join DEPT d on d.id = e.dpt_id
where e.id = 123;
<no rows>
Select
Select
set VALID_TIME(t1);
set TRANS_TIME(t7);
select
e.name
,e.job
,m.name as manager_name
,e.sal
,e.comm
,d.name as dept_name
,d.location
,e.soe as hire_date
,e.eoe as terminate_date
from EMP e
join EMP m on m.id = e.mgr
join DEPT d on d.id = e.dpt_id
where e.id = 123;
<no rows>
Select
Select
set VALID_TIME();
set TRANS_TIME(t7);
select
e.name
,e.job
,m.name as manager_name
,e.sal
,e.comm
,d.name as dept_name
,d.location
,e.soe as hire_date
,e.eoe as terminate_date
from EMP e
join EMP m on m.id = e.mgr
join DEPT d on d.id = e.dpt_id
where e.id = 123;
<Fred, Salesman, Luke, 100000, 2000, Sales, Manchester, soe1, eoe1>
Select
Select
set VALID_TIME(t4);
set TRANS_TIME(t7);
select
e.name
,e.job
,m.name as manager_name
,e.sal
,e.comm
,d.name as dept_name
,d.location
,e.soe as hire_date
,e.eoe as terminate_date
from EMP e
join EMP m on m.id = e.mgr
join DEPT d on d.id = e.dpt_id
where e.id = 123;
<Fred, Salesman, Luke, 100000, 2000, Sales, Manchester, soe1, eoe1>
Select
Select
set VALID_TIME(t5);
set TRANS_TIME(t7);
select
e.name
,e.job
,m.name as manager_name
,e.sal
,e.comm
,d.name as dept_name
,d.location
,e.soe as hire_date
,e.eoe as terminate_date
from EMP e
join EMP m on m.id = e.mgr
join DEPT d on d.id = e.dpt_id
where e.id = 123;
<no rows>
Select
Select
set VALID_TIME(t6);
set TRANS_TIME(t7);
select
e.name
,e.job
,m.name as manager_name
,e.sal
,e.comm
,d.name as dept_name
,d.location
,e.soe as hire_date
,e.eoe as terminate_date
from EMP e
join EMP m on m.id = e.mgr
join DEPT d on d.id = e.dpt_id
where e.id = 123;
<no rows>
Select
Select
set VALID_TIME();
set TRANS_TIME();
select
e.name
,e.job
,m.name as manager_name
,e.sal
,e.comm
,d.name as dept_name
,d.location
,e.soe as hire_date
,e.eoe as terminate_date
from EMP e
join EMP m on m.id = e.mgr
join DEPT d on d.id = e.dpt_id
where e.id = 123;
<Fred, Sales Director, null, 130000, 0, Sales, Manchester, soe3, eoe2>
Select
Select
set VALID_TIME();
set TRANS_TIME(t8);
select
e.name
,e.job
,m.name as manager_name
,e.sal
,e.comm
,d.name as dept_name
,d.location
,e.soe as hire_date
,e.eoe as terminate_date
from EMP e
join EMP m on m.id = e.mgr
join DEPT d on d.id = e.dpt_id
where e.id = 123;
<Fred, Technical Support, John, 130000, 0, IT, London, soe2, eoe2>
Select
Select
set VALID_TIME();
set TRANS_TIME(t7);
select
e.name
,e.job
,m.name as manager_name
,e.sal
,e.comm
,d.name as dept_name
,d.location
,e.soe as hire_date
,e.eoe as terminate_date
from EMP e
join EMP m on m.id = e.mgr
join DEPT d on d.id = e.dpt_id
where e.id = 123;
<Fred, Salesman, Luke, 100000, 2000, Sales, Manchester, soe1, eoe1>
Select
Select
set VALID_TIME();
set TRANS_TIME(t10);
select
e.name
,e.job
,m.name as manager_name
,e.sal
,e.comm
,d.name as dept_name
,d.location
,e.soe as hire_date
,e.eoe as terminate_date
from EMP e
join EMP m on m.id = e.mgr
join DEPT d on d.id = e.dpt_id
where e.id = 123;
<no rows>
Select
Select
set VALID_TIME(t4);
set TRANS_TIME();
select
e.name
,e.job
,m.name as manager_name
,e.sal
,e.comm
,d.name as dept_name
,d.location
,e.soe as hire_date
,e.eoe as terminate_date
from EMP e
join EMP m on m.id = e.mgr
join DEPT d on d.id = e.dpt_id
where e.id = 123;
<Fred, Sales Intern, Luke, 30000, 500, Sales, Manchester, soe3, eoe2>
Select
Select
set VALID_TIME(t4);
set TRANS_TIME(t8);
select
e.name
,e.job
,m.name as manager_name
,e.sal
,e.comm
,d.name as dept_name
,d.location
,e.soe as hire_date
,e.eoe as terminate_date
from EMP e
join EMP m on m.id = e.mgr
join DEPT d on d.id = e.dpt_id
where e.id = 123;
<Fred, Sales Intern, Luke, 30000, 500, Sales, Manchester, soe2, eoe2>
Select
Select
set VALID_TIME(t4);
set TRANS_TIME(t7);
select
e.name
,e.job
,m.name as manager_name
,e.sal
,e.comm
,d.name as dept_name
,d.location
,e.soe as hire_date
,e.eoe as terminate_date
from EMP e
join EMP m on m.id = e.mgr
join DEPT d on d.id = e.dpt_id
where e.id = 123;
<no rows>
Select
Select
set VALID_TIME(t4);
set TRANS_TIME(t10);
select
e.name
,e.job
,m.name as manager_name
,e.sal
,e.comm
,d.name as dept_name
,d.location
,e.soe as hire_date
,e.eoe as terminate_date
from EMP e
join EMP m on m.id = e.mgr
join DEPT d on d.id = e.dpt_id
where e.id = 123;
<no rows>
Insulate from
Insulate from
Complexity
Complexity
Developers don't need to worry about the underlying structure of the history and audit. They can be confident that all versions are stored efficiently, easily accessible and with perfect integrity .
Elevate to
Elevate to
Current View
Current View
Notice how the same query can be run in different time contexts to produce appropriate results.
The query is multi-table, joined by business keys. No complicated date sub-queries required.
Page updated
Google Sites
Report abuse