Solved

PL/SQL

Posted on 2011-09-14
22
300 Views
Last Modified: 2012-06-21
I had 2 tables in oracle
Table1
Name age    date              salary       id
 xxx    25   16-9-2010        15000      1
 yyy    26   16-7-2010         25000      2
 zzz    27    18-9-2010        260000     3

Table2
Name age    date               salary        id
 xxx    25   18-9-2011        18000        1
 yyy    26   16-7-2011         75000        2
 zzz    27    18-9-2011        360000       3

 
Now my output should be

id  Name   age     date          changed_field_name   before_date   before_data   after_data
 1   xxx     23    18-9-2011     salary                           16-9-2010         15000      18000
 2   yyy     26    16-7-2010     salary                           16-7-2011         25000      75000


can i achive this o/p using pl/sql.kindly help me on  this
0
Comment
Question by:Kanigi
  • 7
  • 6
  • 4
  • +3
22 Comments
 
LVL 1

Expert Comment

by:jimmart
ID: 36540694
Assuming you only want to display data and not make updates, and you did not mean to exclude zzz the SQL is:
select Table1.id,
       Table1.Name,  
       Table1.age,  
       Table2.date,  
       "salary" as changed_field_name,  
       Table1.date as before_date,  
       Table1.salary as before_data,  
       Table2.salary as after_data
from Table1
join Table2 on Table1.id = Table2.id
order by Table1.Name
0
 

Author Comment

by:Kanigi
ID: 36540698
HI jimmart,

The field name can be any may be name
0
 
LVL 1

Expert Comment

by:jimmart
ID: 36540920
I am sorry, I did not understand "The field name can be any may be name".
What field name?
Can be any?
Sorry ???
0
 

Author Comment

by:Kanigi
ID: 36541011
Ok  I will just try to make more clear
Question: I had 2 tables in oracle  lets say
Table1
Name age    date              salary       id    streetname
 xxx    25   16-9-2010        15000      1    park street
 yyy    26   16-7-2010         25000      2   james street
 zzz    27    18-9-2010        260000     3  abcstreet

Table2
Name age    date               salary        id   streetname
 xxx    25   18-9-2011        18000        1    xyzstreet
 yyy    26   16-7-2011         75000        2   james street
 zzz    27    18-9-2011        360000       3  richmondstreet

in table 1 for the employee xxx the street name is park street and salary is 15000
and again if you observe the table2 for the employee xxx the street name is xyz street and salar yis 18000.so now the columns street name and salary values has been changed
Now my output should be

id  Name   age     date          changed_field_name   before_date   before_data   after_data
 1   xxx     23    18-9-2011     salary                           16-9-2010         15000          18000
 1   xxx     23    18-9-2011     streetname                   16-9-2010        parkstreet      xyzstreet
 2   yyy     26    16-7-2010     salary                           16-7-2011         25000            75000
 3   ZZZ    27    18-9-2011     salary                            18-9-2010         260000          360000
 3   ZZZ    27    18-9-2011     streetname                     18-9-2010         abcstreet      richmondstreet

so the change_field_name can be any of the columns depend upon the changes in the values
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 36542381
"can be any of the columns"  - this can't be true

at least one of them must be constant.  otherwise how could you link a row in table1 to a row in table2

I'm assuming the constant column is ID, but I'll wait for confirmation before proceeding
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36543002
I'm guessing id is the 'common' field and you want ANY change.

For example even with age,name,etc...

I'm sure there is a more elegant way.  I'm also sure you can probably loop through user_tab_columns and do everything with dynamic SQL.

Here's the quick and dirty approach to the the results in the format you want.

I shortened it so it doesn't do ALL the columns but you get the idea and can extend it.



drop table tab1 purge;
create table tab1(id number, name char(3), age number, date1 date, salary number, streetname varchar2(50));

drop table tab2 purge;
create table tab2(id number, name char(3), age number, date1 date, salary number, streetname varchar2(50));

insert into tab1 values(1,'xxx',25,to_date('16-9-2010','DD-MM-YYYY'), 15000, 'park street');
insert into tab1 values(2,'yyy',26,to_date('16-7-2010','DD-MM-YYYY'), 25000, 'james street');

insert into tab2 values(1,'xxx',25,to_date('18-9-2010','DD-MM-YYYY'), 18000, 'xyzstreet');
insert into tab2 values(2,'yyy',26,to_date('16-7-2010','DD-MM-YYYY'), 75000, 'james street');
commit;

select id,changed_field,before_data,after_data from
(
select t1.id, 'salary' changed_field, to_char(t1.salary) before_data, to_char(t2.salary) after_data from tab1 t1, tab2 t2 where t1.id=t2.id and t1.salary != t2.salary
union all
select t1.id, 'streetname', t1.streetname, t2.streetname from tab1 t1, tab2 t2 where t1.id=t2.id and t1.streetname != t2.streetname
)
order by id
/

Open in new window

0
 
LVL 73

Expert Comment

by:sdstuber
ID: 36545155
I'm just guessing here...

are you expecting all "constant" data to show up as their original columns
but "changed" data to  show up in the "changed_field_name" and before/after columns?

if so, that's not possible in SQL,  the columns of a sql statement must be defined when the query is parsed, but that would require executing the query to find which values changed.


if you are NOT expecting that,  then what do you expect to be in the "name" column if name changes?  same with age
0
 
LVL 4

Expert Comment

by:schubach
ID: 36545465
I was trying to come up with a working PL/SQL example looping through the records, and then looping through the columns for each record, but I couldn't quite get it to compile.  Maybe someone can help me help the original poster:
declare
	cursor loop_cur is select t1.id,t1."date" as beforeDate, t2."date" as afterDate from table1 t1, table2 t2 where t1.id = t2.id;
	cursor shared_columns is
		select t1.column_name
		from user_tab_cols t1
		, user_tab_cols t2
		where t1.table_name = 'TABLE1'
		and t2.table_name = 'TABLE1'
		and t1.column_name = t2.column_name
		and t1.column_name != 'ID';
	table1Value varchar2(1000);
	table2Value varchar2(1000);
	c number;
	dummy number;
 begin
  for employee in loop_cur loop
	for col in shared_columns loop
		c := dbms_sql.open_cursor;
		dbms_sql.parse(c, 'select '||col.column_name||' into :beforeValue from table1 where id = '||employee.id, dbms_sql.native);
		dummy := dbms_sql.execute(c);
		dbms_sql.variable_value(c, 'beforeValue', table1Value);
		dbms_sql.close_cursor(c);
		c := dbms_sql.open_cursor;
		dbms_sql.parse(c, 'select '||col.column_name||' into :afterValue from table2 where id = ' || employee.id,
			DBMS_SQL.NATIVE);
		dbms_sql.bind_variable(c, 'afterValue', table2Value);
		dummy := dbms_sql.execute(c);
		dbms_sql.variable_value(c, 'afterValue', table2Value);
		dbms_sql.close_cursor(c);
		dbms_output.put_line(employee.id || ' ' || employee.afterDate || ' ' || col.column_name || ' '|| employee.beforeDate || ' ' || table1Value || ' ' || table2Value);
	end loop;
  end loop;
 end;
/

Open in new window

The above code gives me a ora-01006 bind variable does not exist.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 36545539
I could help you get your code to compile, but since the asker's description of what he wants conflicts with the examples of expected output it's not possible at this point to say what the goal should really be.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36545540
>>Maybe someone can help me help the original poster

Ask a question here on the site?

I'm also not sure I would use dbms_sql for this.

I started out with an example using user_tab_columns but was going to use execute immediate.
0
 
LVL 6

Expert Comment

by:ianmills2002
ID: 36547728
This is my take on what is required.

SELECT 
TABLE2.id ,
TABLE2.Name,
TABLE2.age,
TABLE2.date,
'Salary'   change_field_name,
TABLE1.date                   before_date,
to_char(TABLE1.salary)  before_data,
to_char(TABLE2.salary)  after_data
FROM TABLE2 JOIN TABLE1
             ON TABLE2.id = TABLE1.id
WHERE TABLE1.salary <> TABLE2.salary
UNION ALL
SELECT 
TABLE2.id ,
TABLE2.Name,
TABLE2.age,
TABLE2.date,
'Salary'   change_field_name,
TABLE1.date             before_date,
TABLE1.streetname  before_data,
TABLE2.streetname  after_data
FROM TABLE2 JOIN TABLE1
             ON TABLE2.id = TABLE1.id
WHERE TABLE1.streetname <> TABLE2.streetname

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Kanigi
ID: 36558622
HI
The ID is the common field name .MAy be union all will b emore expensiv eif they are more than 10 columns to  compare
Can you help how can we acheive this with dynamic sql
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 36559607
please answer the questions above http:#36545155
0
 

Author Comment

by:Kanigi
ID: 36559884
the changed_data_column means the column displays the column names of the table for which the data has been changed

and befor_data and after_data displays the values of the coresponding columns
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36559911
I believe sdstuber is pointing you back to the outstanding question.

Back in your post http:#a36541011 you show name and age in the 'fixed' output.  Can name and age not change between the tables?  If they can, what does that do to the output?
0
 

Author Comment

by:Kanigi
ID: 36564559
if the age column values  ha been changed

Table1
Name age    date              salary       id    streetname
 xxx    23   16-9-2010        15000      1    park street
 yyy    26   16-7-2010         25000      2   james street
 zzz    27    18-9-2010        260000     3  abcstreet

Table2
Name age    date               salary        id   streetname
 xxx    24   18-9-2011        18000        1    xyzstreet
 yyy    26   16-7-2011         75000        2   james street
 zzz    27    18-9-2011        360000       3  richmondstreet



o/p is:-

id  Name   age     date          changed_field_name   before_date   before_data   after_data
 1   xxx     24    18-9-2011     salary                           16-9-2010         15000          18000
 1   xxx     24    18-9-2011     streetname                   16-9-2010        parkstreet      xyzstreet
 1   xxx     24    18-9-2011     age                               16-9-2010           23               24
 2   yyy     26    16-7-2010     salary                           16-7-2011         25000            75000
 3   ZZZ    27    18-9-2011     salary                            18-9-2010         260000          360000
 3   ZZZ    27    18-9-2011     streetname                     18-9-2010         abcstreet      richmondstreet

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36568210
As I mentioned in http:#a36543002

I would do it like below.

You could create a lot of PL/SQL code to loop through dba_tab_columns, extract column names in common between the two tables, gram the data types and then manually compare the columns and return some 'object' value but that is a lot of work when brute force works just as well.
drop table tab1 purge;
create table tab1(id number, name char(3), age number, date1 date, salary number, streetname varchar2(50));

drop table tab2 purge;
create table tab2(id number, name char(3), age number, date1 date, salary number, streetname varchar2(50));

insert into tab1 values(1,'xxx',23,to_date('16-9-2010','DD-MM-YYYY'), 15000, 'park street');
insert into tab1 values(2,'yyy',26,to_date('16-7-2010','DD-MM-YYYY'), 25000, 'james street');
insert into tab1 values(3,'zzz',27,to_date('18-9-2010','DD-MM-YYYY'), 260000, 'abcstreet');

insert into tab2 values(1,'xxx',24,to_date('18-9-2010','DD-MM-YYYY'), 18000, 'xyzstreet');
insert into tab2 values(2,'yyy',26,to_date('16-7-2010','DD-MM-YYYY'), 75000, 'james street');
insert into tab2 values(3,'zzz',27,to_date('18-9-2010','DD-MM-YYYY'), 360000, 'richmondstreet');
commit;

select id, name, age, t2_date, changed_field, before_date, before_data,after_data from
(
select t1.id, t2.name, t2.age, to_char(t2.date1,'DD-MM-YYYY') t2_date, 'salary' changed_field, to_char(t1.date1,'DD-MM-YYYY') before_date, to_char(t1.salary) before_data, to_char(t2.salary) after_data from tab1 t1, tab2 t2 where t1.id=t2.id and t1.salary != t2.salary
union all
select t1.id, t2.name, t2.age, to_char(t2.date1,'DD-MM-YYYY') t2_date, 'streetname' changed_field, to_char(t1.date1,'DD-MM-YYYY') before_date, to_char(t1.streetname) before_data, to_char(t2.streetname) after_data from tab1 t1, tab2 t2 where t1.id=t2.id and t1.streetname != t2.streetname
union all
select t1.id, t2.name, t2.age, to_char(t2.date1,'DD-MM-YYYY') t2_date, 'age' changed_field, to_char(t1.date1,'DD-MM-YYYY') before_date, to_char(t1.age) before_data, to_char(t2.age) after_data from tab1 t1, tab2 t2 where t1.id=t2.id and t1.age != t2.age
)
order by id
/

Open in new window

0
 

Author Comment

by:Kanigi
ID: 36571272
Thanks for the reply,

The PL/SQL code is very much new to me so can you help me on providing some scripts which would be helpful
0
 
LVL 6

Expert Comment

by:ianmills2002
ID: 36571843
Apologies to slightwv, I just realised that my SQL query suggestion was very similar to yours. I was just about to add the UNION ALL to add for age changes and saw your suggestion above already has this done.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36573552
>>so can you help me on providing some scripts which would be helpful

I would not recommend doing this in PL/SQL.  Can you explain what is wrong with the simple SQL I provided that is making you want to go through the headache of PL/SQL?

>>Apologies to slightwv

No problem.
0
 

Author Comment

by:Kanigi
ID: 36708182
Hi Slighht WV,

I had some 80 columns to compare b/w 2 tables.Can we make this possible using simple SQL
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 36709235
>>Can we make this possible using simple SQL

Did you look at my SQL?  You can add as many 'union all' selects to it as you want.

I believe to get the output in the manner you want this is about the most efficient.

If you can change the output requirement then other options might be available like a simple 'MINUS'.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now