Hello
Trying to convert an Oracle SQL which uses "partition" in Left outer join into SQL server as shown below.
select v.table_name
, v.program_no
, v.column_name
, a.creation_timestamp
, a.record_key
, a.before_value
, a.after_value
from audit_010_v v
left outer join audit_data a partition by (table_name, creation_timestamp, record_key)
on a.table_name = v.table_name
and a.column_name = v.column_name ;
-
AUDIT_010_V : This table holds what all columns and tables name the report needs for Audit
-
AUDIT_DATA. : This table holds all the DML operation happen on a table column, basically table and column name and BEFORE and AFTER value for that column with timestamp DDL is as follow:
CREATE TABLE AUDIT_010_V ( TABLE_NAME nvarchar(100), PROGRAM_NO nvarchar(100), COLUMN_NAME nvarchar(100), SEQ_NO nvarchar(100) ); INSERT INTO AUDIT_010_V VALUES ('MSF203','A','BRANCH_CODE','1'); INSERT INTO AUDIT_010_V VALUES ('MSF203','A','BANK_ACCT_NO','2'); INSERT INTO AUDIT_010_V VALUES ('MSF203','B','STMT_CLOSE_DAY','3'); INSERT INTO AUDIT_010_V VALUES ('MSF203','B','NO_OF_DAYS_PAY','4'); INSERT INTO AUDIT_010_V VALUES ('MSF203','B','INV_STMT_IND','5'); INSERT INTO AUDIT_010_V VALUES ('MSF203','B','SETTLE_DISC','6'); INSERT INTO AUDIT_010_V VALUES ('MSF203','B','SETTLE_DAYS','7'); INSERT INTO AUDIT_010_V VALUES ('MSF203','B','SET_DISC_FLAG','8'); INSERT INTO AUDIT_010_V VALUES ('MSF203','B','FRT_DEFAULT','9'); CREATE TABLE AUDIT_DATA ( TABLE_NAME nvarchar(100), COLUMN_NAME nvarchar(100), CREATION_TIMESTAMP nvarchar(100), RECORD_KEY nvarchar(100), BEFORE_VALUE nvarchar(100), AFTER_VALUE nvarchar(100) ); INSERT INTO AUDIT_DATA VALUES ('MSF203','NO_OF_DAYS_PAY','09-JAN-2020 10.26.00','ARKL062784','30','60'); INSERT INTO AUDIT_DATA VALUES ('MSF203','SET_DISC_FLAG','09-JUN-2020 11.06.00','MTNL020823',NULL,'R'); INSERT INTO AUDIT_DATA VALUES ('MSF203','SETTLE_DAYS','09-JUN-2020 11.06.00','MTNL020823','0','20'); INSERT INTO AUDIT_DATA VALUES ('MSF203','SETTLE_DISC','09-JUN-2020 11.06.00','MTNL020823','0','2');
So for a given table name and a Record Key, display all Columns List, populate Before/After a value for the one which exists in AUDIT_DATA table else leave NULL.
Also for the record where an entry doesn't exist in the AUDIT_DATA table, use CREATION_TIMESTAMP for backfill. On all the Grey cell columns there was no DML.
Output needed is as below: