SL23
Programmer
- Nov 3, 2005
- 12
Hi this is very urgent Please give me the solution as early as possible please.
I have a table in oracle when it goes to teradata it should carry 4 additional columns for each table to carry on the versioning:
eff_strt_tsp TIMESTAMP(6),
eff_end_tsp TIMESTAMP(6),
row_stat_cde VARCHAR(3) CHARACTER SET UNICODE NOT CASESPECIFIC,
dw_crte_tsp TIMESTAMP(6)
The versioning logic is the following, for each source row:
if there exists an active row --per PI, eff_end_tsp = high_date, row_stat_cde = ‘A’
if there is a change
expire the existing active row; --set eff_end_tsp to curr_date
insert a new active row; --with eff_strt_tsp = curr_date, eff_end_tsp = high_date, row_stat_cde = ‘A’
else
insert a new active row; --with eff_strt_tsp = curr_date, eff_end_tsp = high_date, row_stat_cde = ‘A’
end if
Note: High_date is defined as 12/31/2899, dw_crte_tsp is set to curr_date when the row is inserted.
I have created a procedure in oracle, Is that some one can help me to do this in teradata.
create or replace procedure ent_ver
(p_tablename VARCHAR2, p_key1 VARCHAR2)
is
v_temp NUMBER;
begin
select 1
into v_temp
from GUI.CUST_ACCT_TYP_DUP
where CUST_ACCT_TYP_CDE = p_key1
and eff_end_tsp = '31-DEC-2899'
and row_stat_cde = 'A';
update GUI.CUST_ACCT_TYP_DUP
set eff_end_tsp = sysdate
where CUST_ACCT_TYP_CDE = p_key1;
commit;
exception
when others
then
null;
end ent_ver;
/
I appreciate your kind help in this... this is very urgent please.
thanks!
I have a table in oracle when it goes to teradata it should carry 4 additional columns for each table to carry on the versioning:
eff_strt_tsp TIMESTAMP(6),
eff_end_tsp TIMESTAMP(6),
row_stat_cde VARCHAR(3) CHARACTER SET UNICODE NOT CASESPECIFIC,
dw_crte_tsp TIMESTAMP(6)
The versioning logic is the following, for each source row:
if there exists an active row --per PI, eff_end_tsp = high_date, row_stat_cde = ‘A’
if there is a change
expire the existing active row; --set eff_end_tsp to curr_date
insert a new active row; --with eff_strt_tsp = curr_date, eff_end_tsp = high_date, row_stat_cde = ‘A’
else
insert a new active row; --with eff_strt_tsp = curr_date, eff_end_tsp = high_date, row_stat_cde = ‘A’
end if
Note: High_date is defined as 12/31/2899, dw_crte_tsp is set to curr_date when the row is inserted.
I have created a procedure in oracle, Is that some one can help me to do this in teradata.
create or replace procedure ent_ver
(p_tablename VARCHAR2, p_key1 VARCHAR2)
is
v_temp NUMBER;
begin
select 1
into v_temp
from GUI.CUST_ACCT_TYP_DUP
where CUST_ACCT_TYP_CDE = p_key1
and eff_end_tsp = '31-DEC-2899'
and row_stat_cde = 'A';
update GUI.CUST_ACCT_TYP_DUP
set eff_end_tsp = sysdate
where CUST_ACCT_TYP_CDE = p_key1;
commit;
exception
when others
then
null;
end ent_ver;
/
I appreciate your kind help in this... this is very urgent please.
thanks!