INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Procedure Please very urgent

Procedure Please very urgent

(OP)
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!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close