6 Replies Latest reply: Jan 4, 2013 9:03 AM by choti RSS

    help two sql queries toload target table

      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