Home » SQL & PL/SQL » SQL & PL/SQL » Compare row & column data (11G)
Compare row & column data [message #668497] Tue, 27 February 2018 13:03 Go to next message
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;

Report message to a moderator

Re: compare rows [message #668501 is a reply to message #668497] Tue, 27 February 2018 13:15 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

https://community.oracle.com/thread/4125169

Report message to a moderator

Compare row & column data [message #668503 is a reply to message #668497] Tue, 27 February 2018 13:46 Go to previous messageGo to next message
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;

Report message to a moderator

Compare row & column data [message #668504 is a reply to message #668497] Tue, 27 February 2018 13:46 Go to previous messageGo to next message
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;

Report message to a moderator

Compare row & column data [message #668505 is a reply to message #668497] Tue, 27 February 2018 13:46 Go to previous messageGo to next message
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;

Report message to a moderator

Re: Compare row & column data [message #668509 is a reply to message #668505] Tue, 27 February 2018 14:14 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
tables AUDIT_T & AUDIT_FINAL contain exactly sames columns.
Why is this the case?
How & why is the data in AUDIT_T different from data in AUDIT_FINAL?

Report message to a moderator

Re: Compare row & column data [message #668511 is a reply to message #668509] Tue, 27 February 2018 14:26 Go to previous messageGo to next message
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');

Report message to a moderator

Re: Compare row & column data [message #668512 is a reply to message #668511] Tue, 27 February 2018 14:28 Go to previous messageGo to next message
bond_8801
Messages: 6
Registered: February 2018
Junior Member
Logic is to compare only between employee and audit_t tables and come up with the changes to insert into audit_final table.

Report message to a moderator

Re: Compare row & column data [message #668516 is a reply to message #668512] Tue, 27 February 2018 17:33 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Table AUDIT_T contain 5 columns; but INSERT statement only populates 4 columns & ignores column OLD_VALUE.
Why is that?

Report message to a moderator

Previous Topic: Count occurrences of String in field
Next Topic: comma separated string to column dynamically
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 18 09:55:10 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.