Home » SQL & PL/SQL » SQL & PL/SQL » Compare row & column data (11G)
Compare row & column data [message #668497] |
Tue, 27 February 2018 13:03 |
|
bond_8801
Messages: 6 Registered: February 2018
|
Junior Member |
|
|
can we do this through PLSQL program. Employee id is the primary key. I have a row in the employee table and all the employee columns in the audit_t table as the rows. Only column value different is
"last_name_changed" from "last_name" (last_name column).
I've to insert into other table audit_final table with the below sql. The challenge is what information is changed here. like last name. captured old value and new value & ignored the columns which are not changed
Insert into AUDIT_FINAL
(HEADER_ID, DETAIL_ID, COLUMN_NAME, NEW_VALUE,OLD_Value)
Values
(12345, 3, 'last_name', 'last_name_changed','last_name');[/b]
Employee table
create table employee(
Employee_ID number(10),
first_name varchar2(100),
Last_name varchar2(200),
Address varchar2(200),
DOB Date,
city varchar2(200)
);
Audit table
create table audit_t(
header_ID number(10),
detail_id number(10),
column_name varchar2(200),
old_val varchar2(200),
new_value varchar2(200)
);
Audit_final table
create table audit_final(
header_ID number(10),
detail_id number(10),
column_name varchar2(200),
old_val varchar2(200),
new_value varchar2(200)
);
Insert into EMPLOYEE
(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, ADDRESS, DOB,
CITY)
Values
(100, 'first_name', 'last_name', 'Address', TO_DATE('01/01/1970', 'MM/DD/YYYY'),
'City');
COMMIT;
Insert into AUDIT_T
(HEADER_ID, DETAIL_ID, COLUMN_NAME, NEW_VALUE)
Values
(12345, 1, 'city', 'City');
Insert into AUDIT_T
(HEADER_ID, DETAIL_ID, COLUMN_NAME, NEW_VALUE)
Values
(12345, 2, 'first_name', 'first_name');
Insert into AUDIT_T
(HEADER_ID, DETAIL_ID, COLUMN_NAME, NEW_VALUE)
Values
(12345, 3, 'last_name', 'last_name_changed');
Insert into AUDIT_T
(HEADER_ID, DETAIL_ID, COLUMN_NAME, NEW_VALUE)
Values
(12345, 4, 'employee_id', '100');
Insert into AUDIT_T
(HEADER_ID, DETAIL_ID, COLUMN_NAME, NEW_VALUE)
Values
(12345, 5, 'address', 'Address');
Insert into AUDIT_T
(HEADER_ID, DETAIL_ID, COLUMN_NAME, NEW_VALUE)
Values
(12345, 6, 'DOB', '01/01/1970');
COMMIT;
|
|
|
|
Compare row & column data [message #668503 is a reply to message #668497] |
Tue, 27 February 2018 13:46 |
|
bond_8801
Messages: 6 Registered: February 2018
|
Junior Member |
|
|
can we do this through PLSQL program. Employee id is the primary key. I have a row in the employee table and all the employee columns in the audit_t table as the rows. Only column value different is
"last_name_changed" from "last_name" (last_name column).
I've to insert into other table audit_final table with the below sql through PLSQL. The challenge is what information is changed here. like last name. captured old value and new value & ignored the columns which are not changed
Insert into AUDIT_FINAL
(HEADER_ID, DETAIL_ID, COLUMN_NAME, NEW_VALUE,OLD_Value)
Values
(12345, 3, 'last_name', 'last_name_changed','last_name');[/b]
Employee table
create table employee(
Employee_ID number(10),
first_name varchar2(100),
Last_name varchar2(200),
Address varchar2(200),
DOB Date,
city varchar2(200)
);
Audit table
create table audit_t(
header_ID number(10),
detail_id number(10),
column_name varchar2(200),
old_val varchar2(200),
new_value varchar2(200)
);
Audit_final table
create table audit_final(
header_ID number(10),
detail_id number(10),
column_name varchar2(200),
old_val varchar2(200),
new_value varchar2(200)
);
Insert into EMPLOYEE
(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, ADDRESS, DOB,
CITY)
Values
(100, 'first_name', 'last_name', 'Address', TO_DATE('01/01/1970', 'MM/DD/YYYY'),
'City');
COMMIT;
Insert into AUDIT_T
(HEADER_ID, DETAIL_ID, COLUMN_NAME, NEW_VALUE)
Values
(12345, 1, 'city', 'City');
Insert into AUDIT_T
(HEADER_ID, DETAIL_ID, COLUMN_NAME, NEW_VALUE)
Values
(12345, 2, 'first_name', 'first_name');
Insert into AUDIT_T
(HEADER_ID, DETAIL_ID, COLUMN_NAME, NEW_VALUE)
Values
(12345, 3, 'last_name', 'last_name_changed');
Insert into AUDIT_T
(HEADER_ID, DETAIL_ID, COLUMN_NAME, NEW_VALUE)
Values
(12345, 4, 'employee_id', '100');
Insert into AUDIT_T
(HEADER_ID, DETAIL_ID, COLUMN_NAME, NEW_VALUE)
Values
(12345, 5, 'address', 'Address');
Insert into AUDIT_T
(HEADER_ID, DETAIL_ID, COLUMN_NAME, NEW_VALUE)
Values
(12345, 6, 'DOB', '01/01/1970');
COMMIT;
|
|
|
Compare row & column data [message #668504 is a reply to message #668497] |
Tue, 27 February 2018 13:46 |
|
bond_8801
Messages: 6 Registered: February 2018
|
Junior Member |
|
|
can we do this through PLSQL program. Employee id is the primary key. I have a row in the employee table and all the employee columns in the audit_t table as the rows. Only column value different is
"last_name_changed" from "last_name" (last_name column).
I've to insert into other table audit_final table with the below sql through PLSQL. The challenge is what information is changed here. like last name. captured old value and new value & ignored the columns which are not changed
Insert into AUDIT_FINAL
(HEADER_ID, DETAIL_ID, COLUMN_NAME, NEW_VALUE,OLD_Value)
Values
(12345, 3, 'last_name', 'last_name_changed','last_name');[/b]
Employee table
create table employee(
Employee_ID number(10),
first_name varchar2(100),
Last_name varchar2(200),
Address varchar2(200),
DOB Date,
city varchar2(200)
);
Audit table
create table audit_t(
header_ID number(10),
detail_id number(10),
column_name varchar2(200),
old_val varchar2(200),
new_value varchar2(200)
);
Audit_final table
create table audit_final(
header_ID number(10),
detail_id number(10),
column_name varchar2(200),
old_val varchar2(200),
new_value varchar2(200)
);
Insert into EMPLOYEE
(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, ADDRESS, DOB,
CITY)
Values
(100, 'first_name', 'last_name', 'Address', TO_DATE('01/01/1970', 'MM/DD/YYYY'),
'City');
COMMIT;
Insert into AUDIT_T
(HEADER_ID, DETAIL_ID, COLUMN_NAME, NEW_VALUE)
Values
(12345, 1, 'city', 'City');
Insert into AUDIT_T
(HEADER_ID, DETAIL_ID, COLUMN_NAME, NEW_VALUE)
Values
(12345, 2, 'first_name', 'first_name');
Insert into AUDIT_T
(HEADER_ID, DETAIL_ID, COLUMN_NAME, NEW_VALUE)
Values
(12345, 3, 'last_name', 'last_name_changed');
Insert into AUDIT_T
(HEADER_ID, DETAIL_ID, COLUMN_NAME, NEW_VALUE)
Values
(12345, 4, 'employee_id', '100');
Insert into AUDIT_T
(HEADER_ID, DETAIL_ID, COLUMN_NAME, NEW_VALUE)
Values
(12345, 5, 'address', 'Address');
Insert into AUDIT_T
(HEADER_ID, DETAIL_ID, COLUMN_NAME, NEW_VALUE)
Values
(12345, 6, 'DOB', '01/01/1970');
COMMIT;
|
|
|
Compare row & column data [message #668505 is a reply to message #668497] |
Tue, 27 February 2018 13:46 |
|
bond_8801
Messages: 6 Registered: February 2018
|
Junior Member |
|
|
can we do this through PLSQL program. Employee id is the primary key. I have a row in the employee table and all the employee columns in the audit_t table as the rows. Only column value different is
"last_name_changed" from "last_name" (last_name column).
I've to insert into other table audit_final table with the below sql through PLSQL. The challenge is what information is changed here. like last name. captured old value and new value & ignored the columns which are not changed
Insert into AUDIT_FINAL
(HEADER_ID, DETAIL_ID, COLUMN_NAME, NEW_VALUE,OLD_Value)
Values
(12345, 3, 'last_name', 'last_name_changed','last_name');[/b]
Employee table
create table employee(
Employee_ID number(10),
first_name varchar2(100),
Last_name varchar2(200),
Address varchar2(200),
DOB Date,
city varchar2(200)
);
Audit table
create table audit_t(
header_ID number(10),
detail_id number(10),
column_name varchar2(200),
old_val varchar2(200),
new_value varchar2(200)
);
Audit_final table
create table audit_final(
header_ID number(10),
detail_id number(10),
column_name varchar2(200),
old_val varchar2(200),
new_value varchar2(200)
);
Insert into EMPLOYEE
(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, ADDRESS, DOB,
CITY)
Values
(100, 'first_name', 'last_name', 'Address', TO_DATE('01/01/1970', 'MM/DD/YYYY'),
'City');
COMMIT;
Insert into AUDIT_T
(HEADER_ID, DETAIL_ID, COLUMN_NAME, NEW_VALUE)
Values
(12345, 1, 'city', 'City');
Insert into AUDIT_T
(HEADER_ID, DETAIL_ID, COLUMN_NAME, NEW_VALUE)
Values
(12345, 2, 'first_name', 'first_name');
Insert into AUDIT_T
(HEADER_ID, DETAIL_ID, COLUMN_NAME, NEW_VALUE)
Values
(12345, 3, 'last_name', 'last_name_changed');
Insert into AUDIT_T
(HEADER_ID, DETAIL_ID, COLUMN_NAME, NEW_VALUE)
Values
(12345, 4, 'employee_id', '100');
Insert into AUDIT_T
(HEADER_ID, DETAIL_ID, COLUMN_NAME, NEW_VALUE)
Values
(12345, 5, 'address', 'Address');
Insert into AUDIT_T
(HEADER_ID, DETAIL_ID, COLUMN_NAME, NEW_VALUE)
Values
(12345, 6, 'DOB', '01/01/1970');
COMMIT;
|
|
|
|
Re: Compare row & column data [message #668511 is a reply to message #668509] |
Tue, 27 February 2018 14:26 |
|
bond_8801
Messages: 6 Registered: February 2018
|
Junior Member |
|
|
Table structure is same but the goal of the PLSQL proc is to add one row into the audit_final table as per below. Out of five rows from audit_t table, we will copy only one column value that is changed which is last name. Old and new value both are populated here.New value derived from audit_t table and old value from employee table. Pls help me.
Insert into AUDIT_FINAL
(HEADER_ID, DETAIL_ID, COLUMN_NAME, NEW_VALUE,OLD_Value)
Values
(12345, 3, 'last_name', 'last_name_changed','last_name');
|
|
|
|
|
Goto Forum:
Current Time: Sat Sep 28 06:45:37 CDT 2024
|