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.OFFENSE_CODE OFFENSE_CODE, ZZ1.OFFENSE_DESC OFFENSE_DESC, ZZ1.SHORT_LIST SHORT_LIST, ZZ1.INTERNAL_USE_ONLY INTERNAL_USE_ONLY, ZZ1.REC_ID REC_ID, ZZ1.CREATOR_ID CREATOR_ID, ZZ1.CREATOR_DATE CREATOR_DATE, ZZ1.UPDATOR_ID UPDATOR_ID, ZZ1.UPDATOR_DATE UPDATOR_DATE, ZZ1.ACTIVE_FLAG ACTIVE_FLAG, ZZ1.OFFENSE_DETAILS OFFENSE_DETAILS, ZZ1.REDACT_MEDIA_VICTIMS REDACT_MEDIA_VICTIMS FROM ZZ_OFFENSE_CODES ZZ1 union all select sysdate, 'C', null, null, ZZ2.OFFENSE_CODE, ZZ2.OFFENSE_DESC, ZZ2.SHORT_LIST, ZZ2.INTERNAL_USE_ONLY, ZZ2.REC_ID, ZZ2.CREATOR_ID, ZZ2.CREATOR_DATE, ZZ2.UPDATOR_ID, ZZ2.UPDATOR_DATE, ZZ2.ACTIVE_FLAG, ZZ2.OFFENSE_DETAILS, ZZ2.REDACT_MEDIA_VICTIMS FROM OFFENSE_CODES ZZ2 where exists ( select * from ZZ_OFFENSE_CODES where OFFENSE_CODE= ZZ2.OFFENSE_CODE )) select AUDIT_TIMESTAMP, AUDIT_TRANSACTION_TYPE, AUDIT_USER_NAME, OFFENSE_CODE, OFFENSE_DESC, SHORT_LIST, INTERNAL_USE_ONLY, REC_ID, CREATOR_ID, CREATOR_DATE, UPDATOR_ID, UPDATOR_DATE, ACTIVE_FLAG, OFFENSE_DETAILS, REDACT_MEDIA_VICTIMS from ( select AUDIT_TIMESTAMP, AUDIT_TRANSACTION_TYPE, AUDIT_USER_NAME, OFFENSE_CODE,( CASE WHEN audit_transaction_type in ( 'C','D') then OFFENSE_DESC ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by OFFENSE_CODE order by audit_timestamp asc),6,1),'N') ='N' THEN lead(OFFENSE_DESC ignore nulls) over (partition by OFFENSE_CODE order by audit_timestamp asc) END) END) OFFENSE_DESC,( CASE WHEN audit_transaction_type in ( 'C','D') then SHORT_LIST ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by OFFENSE_CODE order by audit_timestamp asc),7,1),'N') ='N' THEN lead(SHORT_LIST ignore nulls) over (partition by OFFENSE_CODE order by audit_timestamp asc) END) END) SHORT_LIST,( CASE WHEN audit_transaction_type in ( 'C','D') then INTERNAL_USE_ONLY ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by OFFENSE_CODE order by audit_timestamp asc),8,1),'N') ='N' THEN lead(INTERNAL_USE_ONLY ignore nulls) over (partition by OFFENSE_CODE order by audit_timestamp asc) END) END) INTERNAL_USE_ONLY,( CASE WHEN audit_transaction_type in ( 'C','D') then REC_ID ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by OFFENSE_CODE order by audit_timestamp asc),9,1),'N') ='N' THEN lead(REC_ID ignore nulls) over (partition by OFFENSE_CODE order by audit_timestamp asc) END) END) REC_ID,( CASE WHEN audit_transaction_type in ( 'C','D') then CREATOR_ID ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by OFFENSE_CODE order by audit_timestamp asc),10,1),'N') ='N' THEN lead(CREATOR_ID ignore nulls) over (partition by OFFENSE_CODE 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 OFFENSE_CODE order by audit_timestamp asc),11,1),'N') ='N' THEN lead(CREATOR_DATE ignore nulls) over (partition by OFFENSE_CODE 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 OFFENSE_CODE order by audit_timestamp asc),12,1),'N') ='N' THEN lead(UPDATOR_ID ignore nulls) over (partition by OFFENSE_CODE 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 OFFENSE_CODE order by audit_timestamp asc),13,1),'N') ='N' THEN lead(UPDATOR_DATE ignore nulls) over (partition by OFFENSE_CODE order by audit_timestamp asc) END) END) UPDATOR_DATE,( CASE WHEN audit_transaction_type in ( 'C','D') then ACTIVE_FLAG ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by OFFENSE_CODE order by audit_timestamp asc),14,1),'N') ='N' THEN lead(ACTIVE_FLAG ignore nulls) over (partition by OFFENSE_CODE order by audit_timestamp asc) END) END) ACTIVE_FLAG,( CASE WHEN audit_transaction_type in ( 'C','D') then OFFENSE_DETAILS ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by OFFENSE_CODE order by audit_timestamp asc),15,1),'N') ='N' THEN lead(OFFENSE_DETAILS ignore nulls) over (partition by OFFENSE_CODE order by audit_timestamp asc) END) END) OFFENSE_DETAILS,( CASE WHEN audit_transaction_type in ( 'C','D') then REDACT_MEDIA_VICTIMS ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by OFFENSE_CODE order by audit_timestamp asc),16,1),'N') ='N' THEN lead(REDACT_MEDIA_VICTIMS ignore nulls) over (partition by OFFENSE_CODE order by audit_timestamp asc) END) END) REDACT_MEDIA_VICTIMS,row_number() over (partition by OFFENSE_CODE order by audit_timestamp asc) as rn from t )