All Forums Database
Qamar.Shahbaz 2 posts Joined 06/12
19 Mar 2013
Temporal usage classic scenerio

Hi
I need to track history of party addresses and created a temporal table for this.
CREATE MULTISET TABLE Employee_Addr_Hist (
Name varchar(100),
City varchar (100),
VT PERIOD(DATE) NOT NULL 
)
PRIMARY INDEX(name);
insert into Employee_Addr_Hist (name,city, Validity) values ( 'John','London' ,period( date '2011-01-01', until_changed) );
Now today(2013-03-19) I received a new row from source saying 'John' moved to 'Paris' on '2012-01-01'.
If I use below Update, CURRENT_DATE is used to close previous record and open new. Which is wrong as John moved to Paris on 2012-01-01.
update Employee_Addr_Hist
set  City = 'Paris'
where name = 'John'
and end(VT)  is until_changed
Can anyone help me in this? I just need to use source date for closing old record and opening new using Temporal features.
 
 
 

Tags:
KS42982 137 posts Joined 12/12
19 Mar 2013

You can add your SET statement like below -
SET VT = PERIOD(BEGIN(VT), source_date_column)
 

yiannish 13 posts Joined 06/14
17 Sep 2015

Hello i tried this but is not working. It doesnt allow me to use this on my update stament. Any other suggestion?

You must sign in to leave a comment.