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.TOWING_CO_NAME TOWING_CO_NAME, ZZ1.PHONE_AREA PHONE_AREA, ZZ1.PHONE_PREFIX PHONE_PREFIX, ZZ1.PHONE_SUFFIX PHONE_SUFFIX, ZZ1.TOWING_CO_STREET TOWING_CO_STREET, ZZ1.TOWING_CO_ZIP TOWING_CO_ZIP, ZZ1.TOWING_CO_CITY TOWING_CO_CITY, ZZ1.TOWING_CO_STATE TOWING_CO_STATE, ZZ1.CREATOR_ID CREATOR_ID, ZZ1.UPDATOR_ID UPDATOR_ID, ZZ1.CREATOR_DATE CREATOR_DATE, ZZ1.UPDATOR_DATE UPDATOR_DATE, ZZ1.TOWING_CO_STATE_TYPE TOWING_CO_STATE_TYPE, ZZ1.REC_ID REC_ID, ZZ1.TOWING_CO_ID TOWING_CO_ID, ZZ1.COUNTY_CODE COUNTY_CODE, ZZ1.COUNTY_CODE_TYPE COUNTY_CODE_TYPE FROM ZZ_TOW_COMPANY_CODES ZZ1 union all select sysdate, 'C', null, null, ZZ2.TOWING_CO_NAME, ZZ2.PHONE_AREA, ZZ2.PHONE_PREFIX, ZZ2.PHONE_SUFFIX, ZZ2.TOWING_CO_STREET, ZZ2.TOWING_CO_ZIP, ZZ2.TOWING_CO_CITY, ZZ2.TOWING_CO_STATE, ZZ2.CREATOR_ID, ZZ2.UPDATOR_ID, ZZ2.CREATOR_DATE, ZZ2.UPDATOR_DATE, ZZ2.TOWING_CO_STATE_TYPE, ZZ2.REC_ID, ZZ2.TOWING_CO_ID, ZZ2.COUNTY_CODE, ZZ2.COUNTY_CODE_TYPE FROM TOW_COMPANY_CODES ZZ2 where exists ( select * from ZZ_TOW_COMPANY_CODES where TOWING_CO_ID= ZZ2.TOWING_CO_ID )) select AUDIT_TIMESTAMP, AUDIT_TRANSACTION_TYPE, AUDIT_USER_NAME, TOWING_CO_NAME, PHONE_AREA, PHONE_PREFIX, PHONE_SUFFIX, TOWING_CO_STREET, TOWING_CO_ZIP, TOWING_CO_CITY, TOWING_CO_STATE, CREATOR_ID, UPDATOR_ID, CREATOR_DATE, UPDATOR_DATE, TOWING_CO_STATE_TYPE, REC_ID, TOWING_CO_ID, COUNTY_CODE, COUNTY_CODE_TYPE from ( select AUDIT_TIMESTAMP, AUDIT_TRANSACTION_TYPE, AUDIT_USER_NAME,( CASE WHEN audit_transaction_type in ( 'C','D') then TOWING_CO_NAME ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by TOWING_CO_ID order by audit_timestamp asc),5,1),'N') ='N' THEN lead(TOWING_CO_NAME ignore nulls) over (partition by TOWING_CO_ID order by audit_timestamp asc) END) END) TOWING_CO_NAME,( CASE WHEN audit_transaction_type in ( 'C','D') then PHONE_AREA ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by TOWING_CO_ID order by audit_timestamp asc),6,1),'N') ='N' THEN lead(PHONE_AREA ignore nulls) over (partition by TOWING_CO_ID order by audit_timestamp asc) END) END) PHONE_AREA,( CASE WHEN audit_transaction_type in ( 'C','D') then PHONE_PREFIX ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by TOWING_CO_ID order by audit_timestamp asc),7,1),'N') ='N' THEN lead(PHONE_PREFIX ignore nulls) over (partition by TOWING_CO_ID order by audit_timestamp asc) END) END) PHONE_PREFIX,( CASE WHEN audit_transaction_type in ( 'C','D') then PHONE_SUFFIX ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by TOWING_CO_ID order by audit_timestamp asc),8,1),'N') ='N' THEN lead(PHONE_SUFFIX ignore nulls) over (partition by TOWING_CO_ID order by audit_timestamp asc) END) END) PHONE_SUFFIX,( CASE WHEN audit_transaction_type in ( 'C','D') then TOWING_CO_STREET ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by TOWING_CO_ID order by audit_timestamp asc),9,1),'N') ='N' THEN lead(TOWING_CO_STREET ignore nulls) over (partition by TOWING_CO_ID order by audit_timestamp asc) END) END) TOWING_CO_STREET,( CASE WHEN audit_transaction_type in ( 'C','D') then TOWING_CO_ZIP ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by TOWING_CO_ID order by audit_timestamp asc),10,1),'N') ='N' THEN lead(TOWING_CO_ZIP ignore nulls) over (partition by TOWING_CO_ID order by audit_timestamp asc) END) END) TOWING_CO_ZIP,( CASE WHEN audit_transaction_type in ( 'C','D') then TOWING_CO_CITY ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by TOWING_CO_ID order by audit_timestamp asc),11,1),'N') ='N' THEN lead(TOWING_CO_CITY ignore nulls) over (partition by TOWING_CO_ID order by audit_timestamp asc) END) END) TOWING_CO_CITY,( CASE WHEN audit_transaction_type in ( 'C','D') then TOWING_CO_STATE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by TOWING_CO_ID order by audit_timestamp asc),12,1),'N') ='N' THEN lead(TOWING_CO_STATE ignore nulls) over (partition by TOWING_CO_ID order by audit_timestamp asc) END) END) TOWING_CO_STATE,( CASE WHEN audit_transaction_type in ( 'C','D') then CREATOR_ID ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by TOWING_CO_ID order by audit_timestamp asc),13,1),'N') ='N' THEN lead(CREATOR_ID ignore nulls) over (partition by TOWING_CO_ID order by audit_timestamp asc) END) END) CREATOR_ID,( CASE WHEN audit_transaction_type in ( 'C','D') then UPDATOR_ID ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by TOWING_CO_ID order by audit_timestamp asc),14,1),'N') ='N' THEN lead(UPDATOR_ID ignore nulls) over (partition by TOWING_CO_ID order by audit_timestamp asc) END) END) UPDATOR_ID,( CASE WHEN audit_transaction_type in ( 'C','D') then CREATOR_DATE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by TOWING_CO_ID order by audit_timestamp asc),15,1),'N') ='N' THEN lead(CREATOR_DATE ignore nulls) over (partition by TOWING_CO_ID order by audit_timestamp asc) END) END) CREATOR_DATE,( CASE WHEN audit_transaction_type in ( 'C','D') then UPDATOR_DATE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by TOWING_CO_ID order by audit_timestamp asc),16,1),'N') ='N' THEN lead(UPDATOR_DATE ignore nulls) over (partition by TOWING_CO_ID order by audit_timestamp asc) END) END) UPDATOR_DATE,( CASE WHEN audit_transaction_type in ( 'C','D') then TOWING_CO_STATE_TYPE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by TOWING_CO_ID order by audit_timestamp asc),17,1),'N') ='N' THEN lead(TOWING_CO_STATE_TYPE ignore nulls) over (partition by TOWING_CO_ID order by audit_timestamp asc) END) END) TOWING_CO_STATE_TYPE,( CASE WHEN audit_transaction_type in ( 'C','D') then REC_ID ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by TOWING_CO_ID order by audit_timestamp asc),18,1),'N') ='N' THEN lead(REC_ID ignore nulls) over (partition by TOWING_CO_ID order by audit_timestamp asc) END) END) REC_ID, TOWING_CO_ID,( CASE WHEN audit_transaction_type in ( 'C','D') then COUNTY_CODE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by TOWING_CO_ID order by audit_timestamp asc),20,1),'N') ='N' THEN lead(COUNTY_CODE ignore nulls) over (partition by TOWING_CO_ID order by audit_timestamp asc) END) END) COUNTY_CODE,( CASE WHEN audit_transaction_type in ( 'C','D') then COUNTY_CODE_TYPE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by TOWING_CO_ID order by audit_timestamp asc),21,1),'N') ='N' THEN lead(COUNTY_CODE_TYPE ignore nulls) over (partition by TOWING_CO_ID order by audit_timestamp asc) END) END) COUNTY_CODE_TYPE,row_number() over (partition by TOWING_CO_ID order by audit_timestamp asc) as rn from t )