Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 312
  • Last Modified:

PL/SQL

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
Kanigi
Asked:
Kanigi
  • 7
  • 6
  • 4
  • +3
1 Solution
 
jimmartCommented:
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
 
KanigiAuthor Commented:
HI jimmart,

The field name can be any may be name
0
 
jimmartCommented:
I am sorry, I did not understand "The field name can be any may be name".
What field name?
Can be any?
Sorry ???
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
KanigiAuthor Commented:
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
 
sdstuberCommented:
"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
 
slightwv (䄆 Netminder) Commented:
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
 
sdstuberCommented:
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
 
schubachCommented:
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
 
sdstuberCommented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
ianmills2002Commented:
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
 
KanigiAuthor Commented:
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
 
sdstuberCommented:
please answer the questions above http:#36545155
0
 
KanigiAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
KanigiAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
KanigiAuthor Commented:
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
 
ianmills2002Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
KanigiAuthor Commented:
Hi Slighht WV,

I had some 80 columns to compare b/w 2 tables.Can we make this possible using simple SQL
0
 
slightwv (䄆 Netminder) Commented:
>>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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 7
  • 6
  • 4
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now