Home » SQL & PL/SQL » SQL & PL/SQL » removing duplicate records (merged 2 )
removing duplicate records (merged 2 ) [message #405491] Thu, 28 May 2009 01:13 Go to next message
simi28
Messages: 2
Registered: April 2009
Junior Member
Hi,
I want a query result in which i want to see the empname along with the reporting manager name..but only those employees who reports only one manager.
for this i wrote a query in which i am getting the output like below:

emp mgr
x A
Y B
Y C
Z D
W E
W F

but i want the records only for

X A
Z D
the record for Y and W should exclude as they are reporting more than one mgr.

below is my query
//query starts

select employee,Manager from


(
select distinct
typ, b.element, u.ds Manager, m.ds Employee
from av_element a,
(select 'O' Typ, resp1, parent, parent gr, element
from av_element
where category='ABC'
union
select 'G' , rn, groupcode, e.resp1 , e.element
from av_groupmembers gm, av_element e
where category='ABC'
and gm.groupcode=e.element
union
select 'P' , rn, e.resp1,'xxx' , ra.pool
from av_resavail ra, av_version v, av_element e
where category='ABC'
and v.ver=0
and sd<=sysdate and nvl(fd,'1jan2050') >=sysdate
and ra.pool=v.pool
and v.proj=e.element
and e.category='ABC'
) b,
av_users u, av_users m
where a.category='ABC'
and a.element=b.parent
and a.resp1=u.rn(+)
and b.resp1=m.rn
AND typ = 'O'
)
where 1=1
group by Employee,Manager
having count(manager)=1
//query ends

if i am selecting only employee then i am geting the correct result. but i want to include mgr also which giving me duplicate records for employess.
Can anybody help me in this regard.
Re: removing duplicate records [message #405495 is a reply to message #405491] Thu, 28 May 2009 01:28 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Hi,

Please read posting guidelines as mentioned in OraFAQ Forum Guide.
There are many factors in your query (for instance 3 or more tables and the joins between them) which can affect your output. You have to create a TEST Case which will help people here to recreate your problem. Executable SQL statements (like "CREATE TABLE Statements ..." and "INSERT ..." statements) are best for this purpose.

Go through the guidelines which has everything about how to post clearly explained.

[Edit: Corrected Link]
Regards,
Jo

[Updated on: Thu, 28 May 2009 01:31]

Report message to a moderator

Re: removing duplicate records (merged 2 ) [message #405500 is a reply to message #405491] Thu, 28 May 2009 02:00 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

@Simi28,

SELECT   employee, manager
    FROM (SELECT DISTINCT typ, b.ELEMENT, u.ds manager, m.ds employee
                     FROM av_element a,
                          (SELECT 'O' typ, resp1, PARENT, PARENT gr, ELEMENT
                             FROM av_element
                            WHERE CATEGORY = 'ABC'
                           UNION
                           SELECT 'G', rn, groupcode, e.resp1, e.ELEMENT
                             FROM av_groupmembers gm, av_element e
                            WHERE CATEGORY = 'ABC'
                                  AND gm.groupcode = e.ELEMENT
                           UNION
                           SELECT 'P', rn, e.resp1, 'xxx', ra.pool
                             FROM av_resavail ra, av_version v, av_element e
                            WHERE CATEGORY = 'ABC'
                              AND v.ver = 0
                              AND sd <= SYSDATE
                              AND NVL (fd, '1jan2050') >= SYSDATE
                              AND ra.pool = v.pool
                              AND v.proj = e.ELEMENT
                              AND e.CATEGORY = 'ABC') b,
                          av_users u,
                          av_users m
                    WHERE a.CATEGORY = 'ABC'
                      AND a.ELEMENT = b.PARENT
                      AND a.resp1 = u.rn(+)
                      AND b.resp1 = m.rn
                      AND typ = 'O')
   WHERE 1 = 1
GROUP BY employee, manager
  HAVING COUNT (manager) = 1


From the above query , did u check that the Subquery is giving you a correct result, and you need not go for "Group by employee,manager" i guess.

Have a look at the following query, Since you haven't provided the required DDL , DML I had to generate and get the result,

DDL
CREATE TABLE test_123(empno CHAR(1),mgr CHAR(1));


DML
INSERT INTO TEST_123 ( EMPNO, MGR ) VALUES ( 
'x', 'a'); 
INSERT INTO TEST_123 ( EMPNO, MGR ) VALUES ( 
'y', 'b'); 
INSERT INTO TEST_123 ( EMPNO, MGR ) VALUES ( 
'y', 'c'); 
INSERT INTO TEST_123 ( EMPNO, MGR ) VALUES ( 
'z', 'd'); 
INSERT INTO TEST_123 ( EMPNO, MGR ) VALUES ( 
'w', 'e'); 
INSERT INTO TEST_123 ( EMPNO, MGR ) VALUES ( 
'w', 'f'); 
COMMIT;


SQL Query
SELECT empno, mgr
  FROM test_123
 WHERE empno IN (SELECT   empno
                     FROM test_123
                 GROUP BY empno
                   HAVING COUNT (mgr) = 1)


With you requirement, the above query will give you results
Re: removing duplicate records (merged 2 ) [message #405592 is a reply to message #405491] Thu, 28 May 2009 08:18 Go to previous message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
simi28 wrote on Thu, 28 May 2009 02:13

and sd<=sysdate and nvl(fd,'1jan2050') >=sysdate



This is definitely incorrect. If fd is a DATE, then how could you use a string as the second argument? If it's a string, then you cannot compare a string to a DATE.
Previous Topic: Retrieving alpha numeric data
Next Topic: Sorting alpha numeric data
Goto Forum:
  


Current Time: Wed Dec 07 16:30:47 CST 2016

Total time taken to generate the page: 0.12872 seconds