Row status update in temporal table

Database
Enthusiast

Row status update in temporal table

For a temproal table, if there is status column, such as either "Active" or "Inactive". What's the best practice to update this status colunm?

 

Teradata temporal function will automatic takes care of BEGIN time and END time for period. Once the END time is smaller than current time, the status should change from "Active" to "Inactive".

 

Thanks

Tags (1)

Accepted Solutions
Teradata Employee

Re: Row status update in temporal table


@SeattleData wrote:

I hope I can find better solution rather than redantunt code. To me, your code do the same as my initial code. Sorry if I missed your point.


Put the logic in a view (with a case statement similar to the one I provided), then the applications can use the status and you won't have to physically update a column and it will be always synchronized.

 

1 ACCEPTED SOLUTION
5 REPLIES 5
Enthusiast

Re: Row status update in temporal table

I use below after update:

 

NONSEQUENCED VALIDTIME Update T
SET STATUS = 'Inactive'
WHERE END(Validity) <> '9999-12-31 23:59:59+00:00'
AND STATUS = 'Active';

 

Not sure if there is better approach.

Teradata Employee

Re: Row status update in temporal table

Hi SeattleData,

 

I'd say it's a bad design : redundant and not synchronized with the temporality.

Plus you also have to handle future validity time.

 

At least, calculate the column in a view :

case when Validity contains current_timestamp then 1 else 0 end as flag_Active

 

What are you trying to achieve here with the column ?

Enthusiast

Re: Row status update in temporal table

Waldar:

 

There is status flag column will be used in downstream application. The downstream application will only look at this "status flag" and will not check validtime at all.

 

With Teradata temporal, the end time will be automaticly taken care of when there is update. There is no problem with update/new role. However I need to update the status flag for the "old" row.

 

I hope I can find better solution rather than redantunt code. To me, your code do the same as my initial code. Sorry if I missed your point.

 

Many thanks your replay! 

 

Teradata Employee

Re: Row status update in temporal table


@SeattleData wrote:

I hope I can find better solution rather than redantunt code. To me, your code do the same as my initial code. Sorry if I missed your point.


Put the logic in a view (with a case statement similar to the one I provided), then the applications can use the status and you won't have to physically update a column and it will be always synchronized.

 

Enthusiast

Re: Row status update in temporal table

Smart, I like it.

 

Thanks a lot!