Compare 2 cursors

[barnard90]

I need some suggestion ( or code ) for comparing two cursors

Both cursors fetch employee id and employee name (similar data)

I need to compare the two cursors and identify the common records But I am unable to do so . I need to identify all the common employees in both loops and print them out.
Here is my code.



create or replace procedure compare_cursor as

cursor c1 is select * from employee where hire_date ='10/10/06';

cursor c2 is select * from employee where location ='LONDON';

begin
for x1 in c1
loop
for x2 in c2
loop
if x1.emp_id = x2.emp_id then
dbms_output.put_line(x1.emp_id || ' is repeating ');
end loop;
end loop;
end compare_cursor ;

 

[SantaMufasa]

Barnard,

Hold on...if there is a single record for me, and if my hire_date = '10/10/06' and my location = 'LONDON', then I shall display as a repeating record, right? Is that what you want?

...But I am unable to do so...

What does this mean? Are you receiving a syntax error? Are you receiving a run-time error? Are you receiving unwanted/unexpected/undesirable results? (...Or do not you simply have not access to a computer or Oracle? <grin>)


Also, me thinks that there is an easier way to code what you really want, but first you must clarify what unwanted things are happening to you with the current code. Could you please clarify what condition should exist for your output to read "...' is repeating ' "?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[Turkbear]

Hi,
Why a SP?

In SqlPLus you can do:

Create or replace View common_emps as
select * from employee where hire_date ='10/10/06';
intersect
select * from employee where location ='LONDON';

This view will contain the records that are common to both tables and you can use it to print out the information or whatever..






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SantaMufasa]

Yes, Turkbear, your code is an excellent example of an "easier way to code what (he) really wants", but your code also produces a result set of ALL London hires on October 10, 2006, not just duplicates? I am still hoping that Barard will confirm whether or not this is what he wants.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[LKBrwnDBA]

Don't know if there is a typo or misunderstanding here: the 'cursors' posted by OP and the 'intersect' query posted by tuckbear refer to the SAME TABLE, but the question seems to refer to DIFFERENT tables.

If it is the same table, and if the 'emp_id' is not the PK (or UK), then you could possibly have duplicates.

If there are two tables, then you can use an 'intersect'.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[barnard90]

Actually, I need to print all the records that are duplicating.
I need to capture all the records that are repeating and need to print them as a separate set
Could you please help

 

[SantaMufasa]

Yes, Barnard, I'm certain we can help once we clearly understand your need and the nature of your data.

Are you saying:

1) Duplicate records exist that contain all of the same data in all of the columns including EMP_ID (implying that you have no declared Primary Key)? Or do duplicate records exist that contain all of the same data in all of the columns except EMP_IDs differ?

2) Are a) "10/10/06" and "LONDON" simply sample data or b) your duplicate rows only from "LONDON" on "10/10/06"?

If you can clarify answers to these questions, then I'm certain that we can help you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[Turkbear]

Hi Santa..

Isn't that what he wants ( maybe)?

It seems from his posting that he wants all employees that meet BOTH criteria..

need to identify all the common employees in both loops

( of course, a where clause what uses both criteria would accomplish that as well)

select * from employee where hire_date ='10/10/06'
and location ='LONDON';

This appears to be the same as comparing the records in each cursor to find common entries ( one has 10/10/06 hire_date, one has London locations, so those that are in both meet both criteria)








To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Beilstwh]

or

select emp_id from employee where hire_date ='10/10/06'
and location ='LONDON'
group by emp_id
having count(*) > 1;

Bill
Oracle DBA/Developer
New York State, USA

 

[SantaMufasa]

Turkbear, Unless I misunderstand, Barnard asserts that there are duplicates that he is trying to identify.

If, however, (as I mentioned above) my employee data were not duplicated, yet the location and hire_date where "LONDON" and "10/10/06" respectively, all of the query suggestions appearing so far would contain my non-duplicated row. I don't believe that Barnard wants that. Correct, Barnard?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[SantaMufasa]

...and, Yes, Bill's code would yield a list of emp_ids that represent duplicates.

...and there is some clever code available to DELETE all of the duplicates except for one row, as well. We can post that, Barard, once you confirm what it is you are hoping to do.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[barnard90]

Hi

Thanks to everyone for your posts
I think if I can delete the repeating rows then that is fine

I have my table with records repeating ( actually no primary key is defined ).
How do I delete all the repeating records ( duplicating rows ),
and keep only the original record in the table

Please suggest

The table name is Employee.

 

[Turkbear]

Hi,
As usual, Santa, you got it.,..I should have used Bill's form to identify duplicates...






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Beilstwh]

Barnard, try something like this. Make sure to alter the query to use the correct column. Also make sure to check that at least one row is retained BEFORE you issue the commit.

delete from employee a
where a.emp_id in
(select emp_id from employee
where hire_date ='10/10/06'
and location ='LONDON'
group by emp_id
having count(*) > 1)
and rowid <>
(select max(b.rowid)
from employees b
where a.emp_nr = b.emp_nr);


Bill
Oracle DBA/Developer
New York State, USA

 

[SantaMufasa]

How do I delete all the repeating records ( duplicating rows ), and keep only the original record in the table?

Here is code that confirms proof of concept:


Section 1 -- (a variant of) Bill's code that confirms the number of duplicates:

select emp_id, count(*)
from employee
group by emp_id
having count(*) > 1;

EMP_ID   COUNT(*)
------ ----------
     2          4
    15          3

Section 2 -- Code that identifies the duplicate rows that will go away:

select rowid,emp_id
from employee outer
where rowid > (select min(rowid)
  from employee inner
  where (inner.emp_ID = outer.emp_ID
   or (inner.emp_ID is null
       and outer.emp_ID is null))
  )
/

ROWID                  EMP_ID
------------------ ----------
AAAEjRAAEAAACYCAAZ          2
AAAEjRAAEAAACYCAAa          2
AAAEjRAAEAAACYCAAb          2
AAAEjRAAEAAACYCAAc         15
AAAEjRAAEAAACYCAAd         15

Section 3 -- Code to remove the latest duplicates:

delete from employee outer
where rowid > (select min(rowid)
  from employee inner
  where (inner.emp_ID = outer.emp_ID
   or (inner.emp_ID is null
       and outer.emp_ID is null))
  )
/

5 rows deleted.

Section 4 -- Confirmation that there are no more duplicates:

select emp_id, count(*)
from employee
group by emp_id
having count(*) > 1;

no rows selected

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[Beilstwh]

Dave's query will get rid of ANY duplicated rows in the employee table, mine will only remove the duplicates for the specified hiredate/location. Assuming an employee table should not have any dups, I would suggest that you use dave's solution. I would also make a unique index on the employee id column so this can never happen again.

Bill
Oracle DBA/Developer
New York State, USA

 

[SantaMufasa]

...And one more thought:

Mufasa's First Law of Quality Data: Bad data should live as short a life as possible.

Once you have removed the duplicates, how about executing the following code to ensure that your problem never recurs:

alter table employee add constraint EMP_ID_PK primary key (emp_id);

Table altered.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]