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.CODE CODE, ZZ1.STATUTE STATUTE, ZZ1.CHARGE_CLASS_CODE CHARGE_CLASS_CODE, ZZ1.CHARGE_CATEGORY_CODE CHARGE_CATEGORY_CODE, ZZ1.CHA_DESC CHA_DESC, ZZ1.ADDCHG ADDCHG, ZZ1.APR APR, ZZ1.AOSEQ AOSEQ, ZZ1.ACT ACT, ZZ1.EFFDATE EFFDATE, ZZ1.INADATE INADATE, ZZ1.SOS SOS, ZZ1.BOI BOI, ZZ1.REMARKS REMARKS, ZZ1.INA INA, ZZ1.LUPDATE LUPDATE, ZZ1.SUFFCK SUFFCK, ZZ1.CHAPTER CHAPTER, ZZ1.SECTION SECTION, ZZ1.MVRDATE MVRDATE, ZZ1.STATUTE_NEW STATUTE_NEW, ZZ1.SHORT_LIST SHORT_LIST, ZZ1.OFFNS_CD_OFFENSE_CODE OFFNS_CD_OFFENSE_CODE, ZZ1.DISPLAY_FLAG DISPLAY_FLAG, ZZ1.SPEEDING_FLAG SPEEDING_FLAG, ZZ1.SUSPEND_FLAG SUSPEND_FLAG, ZZ1.VARIABLE_FINE_FLAG VARIABLE_FINE_FLAG, ZZ1.FINE FINE, ZZ1.STATE_CODE STATE_CODE, ZZ1.FINGERPRINT_FLG FINGERPRINT_FLG, ZZ1.ADMIN_CRT ADMIN_CRT, ZZ1.COMMISSARY_RESTRICTED_HOURS COMMISSARY_RESTRICTED_HOURS, ZZ1.RESTRICT_COMMISSARY RESTRICT_COMMISSARY, ZZ1.REC_ID REC_ID, ZZ1.ARREST_CHARGE_CODE ARREST_CHARGE_CODE, ZZ1.DV_FLAG DV_FLAG, ZZ1.REQUIRE_TYPE_FLAG REQUIRE_TYPE_FLAG, ZZ1.REQUIRE_CLASS_FLAG REQUIRE_CLASS_FLAG, ZZ1.DEFAULT_ON_ENTRY_FLAG DEFAULT_ON_ENTRY_FLAG FROM ZZ_CHARGE_CODES ZZ1 union all select sysdate, 'C', null, null, ZZ2.CODE, ZZ2.STATUTE, ZZ2.CHARGE_CLASS_CODE, ZZ2.CHARGE_CATEGORY_CODE, ZZ2.CHA_DESC, ZZ2.ADDCHG, ZZ2.APR, ZZ2.AOSEQ, ZZ2.ACT, ZZ2.EFFDATE, ZZ2.INADATE, ZZ2.SOS, ZZ2.BOI, ZZ2.REMARKS, ZZ2.INA, ZZ2.LUPDATE, ZZ2.SUFFCK, ZZ2.CHAPTER, ZZ2.SECTION, ZZ2.MVRDATE, ZZ2.STATUTE_NEW, ZZ2.SHORT_LIST, ZZ2.OFFNS_CD_OFFENSE_CODE, ZZ2.DISPLAY_FLAG, ZZ2.SPEEDING_FLAG, ZZ2.SUSPEND_FLAG, ZZ2.VARIABLE_FINE_FLAG, ZZ2.FINE, ZZ2.STATE_CODE, ZZ2.FINGERPRINT_FLG, ZZ2.ADMIN_CRT, ZZ2.COMMISSARY_RESTRICTED_HOURS, ZZ2.RESTRICT_COMMISSARY, ZZ2.REC_ID, ZZ2.ARREST_CHARGE_CODE, ZZ2.DV_FLAG, ZZ2.REQUIRE_TYPE_FLAG, ZZ2.REQUIRE_CLASS_FLAG, ZZ2.DEFAULT_ON_ENTRY_FLAG FROM CHARGE_CODES ZZ2 where exists ( select * from ZZ_CHARGE_CODES where CODE= ZZ2.CODE )) select AUDIT_TIMESTAMP, AUDIT_TRANSACTION_TYPE, AUDIT_USER_NAME, CODE, STATUTE, CHARGE_CLASS_CODE, CHARGE_CATEGORY_CODE, CHA_DESC, ADDCHG, APR, AOSEQ, ACT, EFFDATE, INADATE, SOS, BOI, REMARKS, INA, LUPDATE, SUFFCK, CHAPTER, SECTION, MVRDATE, STATUTE_NEW, SHORT_LIST, OFFNS_CD_OFFENSE_CODE, DISPLAY_FLAG, SPEEDING_FLAG, SUSPEND_FLAG, VARIABLE_FINE_FLAG, FINE, STATE_CODE, FINGERPRINT_FLG, ADMIN_CRT, COMMISSARY_RESTRICTED_HOURS, RESTRICT_COMMISSARY, REC_ID, ARREST_CHARGE_CODE, DV_FLAG, REQUIRE_TYPE_FLAG, REQUIRE_CLASS_FLAG, DEFAULT_ON_ENTRY_FLAG from ( select AUDIT_TIMESTAMP, AUDIT_TRANSACTION_TYPE, AUDIT_USER_NAME, CODE,( CASE WHEN audit_transaction_type in ( 'C','D') then STATUTE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by CODE order by audit_timestamp asc),6,1),'N') ='N' THEN lead(STATUTE ignore nulls) over (partition by CODE order by audit_timestamp asc) END) END) STATUTE,( CASE WHEN audit_transaction_type in ( 'C','D') then CHARGE_CLASS_CODE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by CODE order by audit_timestamp asc),7,1),'N') ='N' THEN lead(CHARGE_CLASS_CODE ignore nulls) over (partition by CODE order by audit_timestamp asc) END) END) CHARGE_CLASS_CODE,( CASE WHEN audit_transaction_type in ( 'C','D') then CHARGE_CATEGORY_CODE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by CODE order by audit_timestamp asc),8,1),'N') ='N' THEN lead(CHARGE_CATEGORY_CODE ignore nulls) over (partition by CODE order by audit_timestamp asc) END) END) CHARGE_CATEGORY_CODE,( CASE WHEN audit_transaction_type in ( 'C','D') then CHA_DESC ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by CODE order by audit_timestamp asc),9,1),'N') ='N' THEN lead(CHA_DESC ignore nulls) over (partition by CODE order by audit_timestamp asc) END) END) CHA_DESC,( CASE WHEN audit_transaction_type in ( 'C','D') then ADDCHG ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by CODE order by audit_timestamp asc),10,1),'N') ='N' THEN lead(ADDCHG ignore nulls) over (partition by CODE order by audit_timestamp asc) END) END) ADDCHG,( CASE WHEN audit_transaction_type in ( 'C','D') then APR ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by CODE order by audit_timestamp asc),11,1),'N') ='N' THEN lead(APR ignore nulls) over (partition by CODE order by audit_timestamp asc) END) END) APR,( CASE WHEN audit_transaction_type in ( 'C','D') then AOSEQ ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by CODE order by audit_timestamp asc),12,1),'N') ='N' THEN lead(AOSEQ ignore nulls) over (partition by CODE order by audit_timestamp asc) END) END) AOSEQ,( CASE WHEN audit_transaction_type in ( 'C','D') then ACT ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by CODE order by audit_timestamp asc),13,1),'N') ='N' THEN lead(ACT ignore nulls) over (partition by CODE order by audit_timestamp asc) END) END) ACT,( CASE WHEN audit_transaction_type in ( 'C','D') then EFFDATE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by CODE order by audit_timestamp asc),14,1),'N') ='N' THEN lead(EFFDATE ignore nulls) over (partition by CODE order by audit_timestamp asc) END) END) EFFDATE,( CASE WHEN audit_transaction_type in ( 'C','D') then INADATE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by CODE order by audit_timestamp asc),15,1),'N') ='N' THEN lead(INADATE ignore nulls) over (partition by CODE order by audit_timestamp asc) END) END) INADATE,( CASE WHEN audit_transaction_type in ( 'C','D') then SOS ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by CODE order by audit_timestamp asc),16,1),'N') ='N' THEN lead(SOS ignore nulls) over (partition by CODE order by audit_timestamp asc) END) END) SOS,( CASE WHEN audit_transaction_type in ( 'C','D') then BOI ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by CODE order by audit_timestamp asc),17,1),'N') ='N' THEN lead(BOI ignore nulls) over (partition by CODE order by audit_timestamp asc) END) END) BOI,( CASE WHEN audit_transaction_type in ( 'C','D') then REMARKS ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by CODE order by audit_timestamp asc),18,1),'N') ='N' THEN lead(REMARKS ignore nulls) over (partition by CODE order by audit_timestamp asc) END) END) REMARKS,( CASE WHEN audit_transaction_type in ( 'C','D') then INA ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by CODE order by audit_timestamp asc),19,1),'N') ='N' THEN lead(INA ignore nulls) over (partition by CODE order by audit_timestamp asc) END) END) INA,( CASE WHEN audit_transaction_type in ( 'C','D') then LUPDATE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by CODE order by audit_timestamp asc),20,1),'N') ='N' THEN lead(LUPDATE ignore nulls) over (partition by CODE order by audit_timestamp asc) END) END) LUPDATE,( CASE WHEN audit_transaction_type in ( 'C','D') then SUFFCK ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by CODE order by audit_timestamp asc),21,1),'N') ='N' THEN lead(SUFFCK ignore nulls) over (partition by CODE order by audit_timestamp asc) END) END) SUFFCK,( CASE WHEN audit_transaction_type in ( 'C','D') then CHAPTER ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by CODE order by audit_timestamp asc),22,1),'N') ='N' THEN lead(CHAPTER ignore nulls) over (partition by CODE order by audit_timestamp asc) END) END) CHAPTER,( CASE WHEN audit_transaction_type in ( 'C','D') then SECTION ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by CODE order by audit_timestamp asc),23,1),'N') ='N' THEN lead(SECTION ignore nulls) over (partition by CODE order by audit_timestamp asc) END) END) SECTION,( CASE WHEN audit_transaction_type in ( 'C','D') then MVRDATE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by CODE order by audit_timestamp asc),24,1),'N') ='N' THEN lead(MVRDATE ignore nulls) over (partition by CODE order by audit_timestamp asc) END) END) MVRDATE,( CASE WHEN audit_transaction_type in ( 'C','D') then STATUTE_NEW ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by CODE order by audit_timestamp asc),25,1),'N') ='N' THEN lead(STATUTE_NEW ignore nulls) over (partition by CODE order by audit_timestamp asc) END) END) STATUTE_NEW,( CASE WHEN audit_transaction_type in ( 'C','D') then SHORT_LIST ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by CODE order by audit_timestamp asc),26,1),'N') ='N' THEN lead(SHORT_LIST ignore nulls) over (partition by CODE order by audit_timestamp asc) END) END) SHORT_LIST,( CASE WHEN audit_transaction_type in ( 'C','D') then OFFNS_CD_OFFENSE_CODE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by CODE order by audit_timestamp asc),27,1),'N') ='N' THEN lead(OFFNS_CD_OFFENSE_CODE ignore nulls) over (partition by CODE order by audit_timestamp asc) END) END) OFFNS_CD_OFFENSE_CODE,( CASE WHEN audit_transaction_type in ( 'C','D') then DISPLAY_FLAG ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by CODE order by audit_timestamp asc),28,1),'N') ='N' THEN lead(DISPLAY_FLAG ignore nulls) over (partition by CODE order by audit_timestamp asc) END) END) DISPLAY_FLAG,( CASE WHEN audit_transaction_type in ( 'C','D') then SPEEDING_FLAG ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by CODE order by audit_timestamp asc),29,1),'N') ='N' THEN lead(SPEEDING_FLAG ignore nulls) over (partition by CODE order by audit_timestamp asc) END) END) SPEEDING_FLAG,( CASE WHEN audit_transaction_type in ( 'C','D') then SUSPEND_FLAG ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by CODE order by audit_timestamp asc),30,1),'N') ='N' THEN lead(SUSPEND_FLAG ignore nulls) over (partition by CODE order by audit_timestamp asc) END) END) SUSPEND_FLAG,( CASE WHEN audit_transaction_type in ( 'C','D') then VARIABLE_FINE_FLAG ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by CODE order by audit_timestamp asc),31,1),'N') ='N' THEN lead(VARIABLE_FINE_FLAG ignore nulls) over (partition by CODE order by audit_timestamp asc) END) END) VARIABLE_FINE_FLAG,( CASE WHEN audit_transaction_type in ( 'C','D') then FINE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by CODE order by audit_timestamp asc),32,1),'N') ='N' THEN lead(FINE ignore nulls) over (partition by CODE order by audit_timestamp asc) END) END) FINE,( CASE WHEN audit_transaction_type in ( 'C','D') then STATE_CODE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by CODE order by audit_timestamp asc),33,1),'N') ='N' THEN lead(STATE_CODE ignore nulls) over (partition by CODE order by audit_timestamp asc) END) END) STATE_CODE,( CASE WHEN audit_transaction_type in ( 'C','D') then FINGERPRINT_FLG ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by CODE order by audit_timestamp asc),34,1),'N') ='N' THEN lead(FINGERPRINT_FLG ignore nulls) over (partition by CODE order by audit_timestamp asc) END) END) FINGERPRINT_FLG,( CASE WHEN audit_transaction_type in ( 'C','D') then ADMIN_CRT ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by CODE order by audit_timestamp asc),35,1),'N') ='N' THEN lead(ADMIN_CRT ignore nulls) over (partition by CODE order by audit_timestamp asc) END) END) ADMIN_CRT,( CASE WHEN audit_transaction_type in ( 'C','D') then COMMISSARY_RESTRICTED_HOURS ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by CODE order by audit_timestamp asc),36,1),'N') ='N' THEN lead(COMMISSARY_RESTRICTED_HOURS ignore nulls) over (partition by CODE order by audit_timestamp asc) END) END) COMMISSARY_RESTRICTED_HOURS,( CASE WHEN audit_transaction_type in ( 'C','D') then RESTRICT_COMMISSARY ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by CODE order by audit_timestamp asc),37,1),'N') ='N' THEN lead(RESTRICT_COMMISSARY ignore nulls) over (partition by CODE order by audit_timestamp asc) END) END) RESTRICT_COMMISSARY,( CASE WHEN audit_transaction_type in ( 'C','D') then REC_ID ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by CODE order by audit_timestamp asc),38,1),'N') ='N' THEN lead(REC_ID ignore nulls) over (partition by CODE order by audit_timestamp asc) END) END) REC_ID,( CASE WHEN audit_transaction_type in ( 'C','D') then ARREST_CHARGE_CODE ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by CODE order by audit_timestamp asc),39,1),'N') ='N' THEN lead(ARREST_CHARGE_CODE ignore nulls) over (partition by CODE order by audit_timestamp asc) END) END) ARREST_CHARGE_CODE,( CASE WHEN audit_transaction_type in ( 'C','D') then DV_FLAG ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by CODE order by audit_timestamp asc),40,1),'N') ='N' THEN lead(DV_FLAG ignore nulls) over (partition by CODE order by audit_timestamp asc) END) END) DV_FLAG,( CASE WHEN audit_transaction_type in ( 'C','D') then REQUIRE_TYPE_FLAG ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by CODE order by audit_timestamp asc),41,1),'N') ='N' THEN lead(REQUIRE_TYPE_FLAG ignore nulls) over (partition by CODE order by audit_timestamp asc) END) END) REQUIRE_TYPE_FLAG,( CASE WHEN audit_transaction_type in ( 'C','D') then REQUIRE_CLASS_FLAG ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by CODE order by audit_timestamp asc),42,1),'N') ='N' THEN lead(REQUIRE_CLASS_FLAG ignore nulls) over (partition by CODE order by audit_timestamp asc) END) END) REQUIRE_CLASS_FLAG,( CASE WHEN audit_transaction_type in ( 'C','D') then DEFAULT_ON_ENTRY_FLAG ELSE (CASE WHEN NVL(SUBSTR(lead(AUDIT_TRUE_NULLS) over (partition by CODE order by audit_timestamp asc),43,1),'N') ='N' THEN lead(DEFAULT_ON_ENTRY_FLAG ignore nulls) over (partition by CODE order by audit_timestamp asc) END) END) DEFAULT_ON_ENTRY_FLAG,row_number() over (partition by CODE order by audit_timestamp asc) as rn from t )