Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Procedure Please very urgent

Status
Not open for further replies.

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!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top