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:
|4509371||7352394||16/ABCD/123456||2||6||7/17/2013||Curfman||1968 camaro dr||muscle||AL|
I appreciate your time and looking.
Here is what I cam up with - not sure how well this will perform. I am running this against about 100k records.
WHERE GTEST.LOCATION IS NOT NULL ),
WHERE GTEST.ADDRESS IS NOT NULL),
WHERE GTEST.CITY IS NOT NULL),
WHERE GTEST.STATE IS NOT NULL)
FROM ASAP.GTEST GTEST
WHERE (GTEST.ISS_STAT = '2') AND (GTEST.STATUS = '6')
I have a table that I am trying to capture information from multiple versions of the same record
Then post sample data that actually shows 'multiple versions of the same record'.
The data you posted is for two DIFFERENT 'doc' and 'id' values so why do you say they represent the same row?
And it isn't clear if NULL is a valid value for location, address, city and state. If not then why does the second row have NULL for those rather than duplicate the data from the previous row?