Home » Developer & Programmer » Forms » displaying duplicate data in the data base on a form
displaying duplicate data in the data base on a form [message #83208] Thu, 21 August 2003 11:35 Go to next message
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 Go to previous messageGo to next message
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 #83223 is a reply to message #83211] Fri, 22 August 2003 15:07 Go to previous messageGo to next message
Avadbala
Messages: 17
Registered: October 2002
Junior Member
I have an emp block on a from and i wanted todisplay it on the form if there are 2 duplicate rows if i enter emp no the 2 records have to be displayed on the 2 tabular records.
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 Go to previous message
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.
Previous Topic: Writing to Flat files in Forms 6i
Next Topic: Word and Oracle Reports6i
Goto Forum:
  


Current Time: Fri Mar 29 04:34:25 CDT 2024