OraFAQ Forum: SQL & PL/SQL » eliminate duplicate records in reporting

Home » SQL & PL/SQL » SQL & PL/SQL » eliminate duplicate records in reporting

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

eliminate duplicate records in reporting [message #1921]

Tue, 11 June 2002 08:57

Godwin
Messages: 37
Registered: January 2002

Member

Hi there,
I have 2 tables(1:many relationship)with records e.g
Personnel(Pnumber,name)
Qualification(Pnumber,qual,earn_date)
with records...
Pnumber name
1000 Ofoe
1001 John

Pnumber qual earn_date
1000 B.A sydate
1000 MBA sysdate+365
1001 Bsc sysdate
1001 Phd sysdate+365
.......
and now i want a report in this format:(i.e the name of the staff must appear only once for the two qualifications)
Report 1
name qualification
Ofoe B.A
MBA
John Bsc
Phd

Report 2
name qualification Date
Ofoe MBA sysdate+365
John Phd sysdate+365

NB. Report 2 should use the earn_date as a condition to produce the highest qualification of each staff.
Please give me the query to do the above.
thanks.

Report message to a moderator

Re: eliminate duplicate records in reporting [message #1922 is a reply to message #1921]

Tue, 11 June 2002 09:12

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

I beleive , you can use BREAK ON, to eliminate the repeated rows.
something like following

SQL> ed
Wrote file afiedt.buf

  1  select deptno,ename from emp
  2* order by deptno
  3  /

    DEPTNO ENAME
---------- ----------
        10 CLARK
        10 KING
        10 MILLER
        20 SMITH
        20 ADAMS
        20 FORD
        20 SCOTT
        20 JONES
        30 ALLEN
        30 BLAKE
        30 MARTIN
        30 JAMES
        30 TURNER
        30 WARD

14 rows selected.

SQL> break on deptno
SQL> /

    DEPTNO ENAME
---------- ----------
        10 CLARK
           KING
           MILLER
        20 SMITH
           ADAMS
           FORD
           SCOTT
           JONES
        30 ALLEN
           BLAKE
           MARTIN
           JAMES
           TURNER
           WARD

14 rows selected.

SQL>

Report message to a moderator

Previous Topic:	Re: accessing system variables
Next Topic:	Generating DDLs of Existing tables

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon May 20 13:02:16 CDT 2024