10 Replies Latest reply on Sep 30, 2013 9:32 PM by rp0428

    capture data from previous version of record

    GMoney

      Good day all,

      I have a table that I am trying to capture information from multiple versions of the same record. In this example the record I want is the one that shows iss_stat of '2' and status of '6'. However, since the fields location, address, city and state are empty, I need to capture that information from a previous version where those fields are populated.

       

      create table GTEST

      ( doc VARCHAR2(50), id VARCHAR2(50), cc_id VARCHAR2(50), iss_stat CHAR(1), status CHAR(1), rpt_date DATE, location VARCHAR2(50), address VARCHAR2(50), city VARCHAR2(25), state VARCHAR2(25));

      INSERT INTO GTEST VALUES ('0409371','5260294','16/ABCD/123456','2','8',(TO_DATE('2013/07/17 10:45:29', 'yyyy/mm/dd hh24:mi:ss')),'Curfman','1968 camaro dr','muscle','AL');

      INSERT INTO GTEST VALUES ('4509371','7352394','16/ABCD/123456','2','6',(TO_DATE('2013/07/17 11:25:17', 'yyyy/mm/dd hh24:mi:ss')),NULL,NULL,NULL,NULL);

       

      In this case my desired result would be:

       

      DOCIDCC_IDISS_STATSTATUSRPT_DATELOCATIONADDRESSCITYSTATE
      4509371735239416/ABCD/123456267/17/2013Curfman1968 camaro drmuscleAL

       

      I appreciate your time and looking.

       

      Thanks,

       

      G