This discussion is archived
6 Replies Latest reply: Jan 4, 2013 7:03 AM by choti RSS

help two sql queries toload target table

choti Newbie
Currently Being Moderated
 
create table table1 
    (code varchar2(10)
     ,mod_time date);
     
     insert into table1 values('2533',to_date('31-JUL-2012', 'DD-MON-YY'));
     insert into table1 values('2534',to_date('31-JUL-2012', 'DD-MON-YY'));
      insert into table1 values('2535',to_date('01-SEP-2012', 'DD-MON-YY'));
    
      create table table2
    (code varchar2(10)
     ,ID   NUMBER
     ,TYPE VARCHAR2(3)
     ,mod_time date);
     insert into table2 values('2533',1,'AB',to_date('01-SEP-2012', 'DD-MON-YY'));
     insert into table2 values('2534',1,'CD',to_date('01-SEP-2012', 'DD-MON-YY'));
     
     create table table3
     (ID   NUMBER
      ,mod_time date);
      insert into table3 values(1,to_date('01-SEP-2012', 'DD-MON-YY'));
      insert into table3 values(2,to_date('01-SEP-2012', 'DD-MON-YY'));
      
      create table target
      (code varchar2(10)
      ,ID   NUMBER
      ,TYPE VARCHAR2(3)
      ,table1_modtime date
      ,table2_modtime date
      ,table3_modtime date
      ,valid_till  date 
      ,valid_from  date 
      drop table target
      
      combine all the three tables where table1.mod_time<=31-jul-2012, table2.mod_time<=31-jul-2012,table3.mod_time<=31-jul-2012
      
      initial load to target load should be get the information from all the tables modtime<=31-jul-2012
      Need to have select query to get the below 1st load expected output
      valid_till defaults to to_date('31-DEC-2030', 'DD-MON-YY')
      valid_from defaults to to_date('01-01-1995', 'DD-MON-YY')
      
     1st load expected output
      code    id     type  table1_modtime   table2_modtime      table3.modtime  valid_from valid_till   
      2533                 31-JUL-2012                                                            01-01-1995  31-DEC-2030  
      2534                 31-JUL-2012                                                             01-01-1995   31-DEC-2030  
      -------------------------------------------------------------------------------------------------------
for the second load. first get the query to get the values from all the tables
mod_time>31-jul-2012 .

if the code exists then update the existing record with valid_till to 02-jan-2013 i.e yesterday
and insert new record with the new values and valid from date to 03-jan-2013 i.e today
    --------------------2nd load expected output  
      code    id     type  table1_modtime   table2_modtime      table3.modtime      valid_from     valid_till
      2533                 31-JUL-2012                                                               01-01-1995       02-jan-2013 --updating old record valid-till to yesterday
      2534                 31-JUL-2012                                                               01-01-1995       02-jan-2013  --updating old record valid-till to yesterday
      2533      1     AB   31-JUL-2012        01-SEP-2012        01-SEP-2012         03-jan-2013  31-DEC-2030----updating old record valid-from to today
      2534      1     CD   31-JUL-2012        01-SEP-2012       01-SEP-2012          03-jan-2013  31-DEC-2030--updating old record valid-from to today
      2535                  01-SEP-2012                                                              01-01-1995  31-DEC-2030 --no record exist so insert new record in target table
      --------------------------------------------------------------------------------------------------------
  
Edited by: choti on Jan 3, 2013 5:01 PM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points