displaying duplicate data in the data base on a form [message #83208] |
Thu, 21 August 2003 11:35 |
Avadbala
Messages: 17 Registered: October 2002
|
Junior Member |
|
|
Hi guys
I am trying to display the emp details on the employee form.. but my data base consists of duplicate emp numbers( say we dont have a primary key)
how do i display all the duplicate emp details given the empno.
Thanks
Avad
|
|
|
Re: displaying duplicate data in the data base on a form [message #83211 is a reply to message #83208] |
Thu, 21 August 2003 23:46 |
Reema
Messages: 50 Registered: July 2003
|
Member |
|
|
Hi Avad,
You can display all the duplicate rows on the basis of empno using the following query.In case you want to delete it you can do the same.You will have to use "Delete from emp" instead of "Select empno,ename from emp" . I hope it helps.
select empno,ename from emp
where rowid not in (select max(rowid) from emp
group by empno);
|
|
|
|
Re: displaying duplicate data in the data base on a form [message #83228 is a reply to message #83223] |
Mon, 25 August 2003 00:45 |
Shailender Mehta
Messages: 49 Registered: June 2003
|
Member |
|
|
Try the following :-
1) I have used table TMP1 below for example :
Name Null? Type
------------------------------- -------- ----
EMP_NO VARCHAR2(10)
EID NUMBER
EMP_NO EID
---------- ---------
E1001 1
E1001 2
E1001 3
E1001 4
E1001 5
E1002 3
6 rows selected.
As per your requirement, you want to display data if the emp_id has 2 duplicate rows or more
Solution
---------
In the Where clause (Block Property), type in the following where condition :
Emp_No In ( Select Emp_No
From Tmp1
Group By Emp_No
Having Count(*) > 2
)
This will display Employee Id's ONLY if it has duplicate rows. Taking the above example
Emp_id 'E1002' will not be displayed.
|
|
|