with t as (SELECT ZZ1.AUDIT_TIMESTAMP AUDIT_TIMESTAMP, ZZ1.AUDIT_TRANSACTION_TYPE AUDIT_TRANSACTION_TYPE, ZZ1.AUDIT_USER_NAME AUDIT_USER_NAME, ZZ1.AUDIT_TRUE_NULLS AUDIT_TRUE_NULLS, ZZ1.DATASETID DATASETID, ZZ1.ATT_ID ATT_ID, ZZ1.KEYWORD KEYWORD, ZZ1.DATE_OF_INFO DATE_OF_INFO, ZZ1.FILE_DESC FILE_DESC, ZZ1.FILE_CODE FILE_CODE, ZZ1.FILE_CODE_TYPE FILE_CODE_TYPE, ZZ1.CREATOR_ID CREATOR_ID, ZZ1.CREATOR_DATE CREATOR_DATE, ZZ1.UPDATOR_ID UPDATOR_ID, ZZ1.UPDATOR_DATE UPDATOR_DATE, ZZ1.REC_ID REC_ID, ZZ1.EXTSYS_CODE EXTSYS_CODE FROM ZZ_DS_ICR_FILE_REFERENCES ZZ1 union all select sysdate, 'C', null, null, ZZ2.DATASETID, ZZ2.ATT_ID, ZZ2.KEYWORD, ZZ2.DATE_OF_INFO, ZZ2.FILE_DESC, ZZ2.FILE_CODE, ZZ2.FILE_CODE_TYPE, ZZ2.CREATOR_ID, ZZ2.CREATOR_DATE, ZZ2.UPDATOR_ID, ZZ2.UPDATOR_DATE, ZZ2.REC_ID, ZZ2.EXTSYS_CODE FROM DS_ICR_FILE_REFERENCES ZZ2 where exists ( select * from ZZ_DS_ICR_FILE_REFERENCES where DATASETID= ZZ2.DATASETID AND ATT_ID= ZZ2.ATT_ID )) select AUDIT_TIMESTAMP, AUDIT_TRANSACTION_TYPE, AUDIT_USER_NAME, DATASETID, ATT_ID, KEYWORD, DATE_OF_INFO, FILE_DESC, FILE_CODE, FILE_CODE_TYPE, CREATOR_ID, CREATOR_DATE, UPDATOR_ID, UPDATOR_DATE, REC_ID, EXTSYS_CODE from ( select AUDIT_TIMESTAMP, AUDIT_TRANSACTION_TYPE, AUDIT_USER_NAME, DATASETID, ATT_ID,( CASE WHEN audit_transaction_type in ( 'C','D') then KEYWORD ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by DATASETID,ATT_ID order by audit_timestamp asc),7,1),'N') ='N' THEN lead(KEYWORD ignore nulls) over (partition by DATASETID,ATT_ID order by audit_timestamp asc) END) END) KEYWORD,( CASE WHEN audit_transaction_type in ( 'C','D') then DATE_OF_INFO ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by DATASETID,ATT_ID order by audit_timestamp asc),8,1),'N') ='N' THEN lead(DATE_OF_INFO ignore nulls) over (partition by DATASETID,ATT_ID order by audit_timestamp asc) END) END) DATE_OF_INFO,( CASE WHEN audit_transaction_type in ( 'C','D') then FILE_DESC ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by DATASETID,ATT_ID order by audit_timestamp asc),9,1),'N') ='N' THEN lead(FILE_DESC ignore nulls) over (partition by DATASETID,ATT_ID order by audit_timestamp asc) END) END) FILE_DESC,( CASE WHEN audit_transaction_type in ( 'C','D') then FILE_CODE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by DATASETID,ATT_ID order by audit_timestamp asc),10,1),'N') ='N' THEN lead(FILE_CODE ignore nulls) over (partition by DATASETID,ATT_ID order by audit_timestamp asc) END) END) FILE_CODE,( CASE WHEN audit_transaction_type in ( 'C','D') then FILE_CODE_TYPE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by DATASETID,ATT_ID order by audit_timestamp asc),11,1),'N') ='N' THEN lead(FILE_CODE_TYPE ignore nulls) over (partition by DATASETID,ATT_ID order by audit_timestamp asc) END) END) FILE_CODE_TYPE,( CASE WHEN audit_transaction_type in ( 'C','D') then CREATOR_ID ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by DATASETID,ATT_ID order by audit_timestamp asc),12,1),'N') ='N' THEN lead(CREATOR_ID ignore nulls) over (partition by DATASETID,ATT_ID order by audit_timestamp asc) END) END) CREATOR_ID,( CASE WHEN audit_transaction_type in ( 'C','D') then CREATOR_DATE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by DATASETID,ATT_ID order by audit_timestamp asc),13,1),'N') ='N' THEN lead(CREATOR_DATE ignore nulls) over (partition by DATASETID,ATT_ID order by audit_timestamp asc) END) END) CREATOR_DATE,( CASE WHEN audit_transaction_type in ( 'C','D') then UPDATOR_ID ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by DATASETID,ATT_ID order by audit_timestamp asc),14,1),'N') ='N' THEN lead(UPDATOR_ID ignore nulls) over (partition by DATASETID,ATT_ID order by audit_timestamp asc) END) END) UPDATOR_ID,( CASE WHEN audit_transaction_type in ( 'C','D') then UPDATOR_DATE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by DATASETID,ATT_ID order by audit_timestamp asc),15,1),'N') ='N' THEN lead(UPDATOR_DATE ignore nulls) over (partition by DATASETID,ATT_ID order by audit_timestamp asc) END) END) UPDATOR_DATE,( CASE WHEN audit_transaction_type in ( 'C','D') then REC_ID ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by DATASETID,ATT_ID order by audit_timestamp asc),16,1),'N') ='N' THEN lead(REC_ID ignore nulls) over (partition by DATASETID,ATT_ID order by audit_timestamp asc) END) END) REC_ID,( CASE WHEN audit_transaction_type in ( 'C','D') then EXTSYS_CODE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by DATASETID,ATT_ID order by audit_timestamp asc),17,1),'N') ='N' THEN lead(EXTSYS_CODE ignore nulls) over (partition by DATASETID,ATT_ID order by audit_timestamp asc) END) END) EXTSYS_CODE,row_number() over (partition by DATASETID,ATT_ID order by audit_timestamp asc) as rn from t )