Home » SQL & PL/SQL » SQL & PL/SQL » querying exception records
querying exception records [message #260528] Mon, 20 August 2007 04:23 Go to next message
sandhyaa
Messages: 79
Registered: February 2007
Member
Hi All
I have a table which contain some in-consistent records. I need to query those.

The table tableMaster has two fields

ID NUMBER
EMPLOYEE_NUMBER VARCHAR2(10)

There could be more than one record with same ID and EMPLOYEE_NUMBER combination, but there cannot be a different
ID for same employee_number, I need to identify such set of records, for example

ID	EMPLOYEE_NUMBER
1	A_0001
1	A_0001
2	B_0001
3	C_0001
4	D_0001
2       B_0001
9	A_0001



Invalid Set

ID	EMPLOYEE_NUMBER
1	A_0001
9	A_0001


I need to identify such invalid sets from the table?


Regards,
Sandi
Re: querying exception records [message #260530 is a reply to message #260528] Mon, 20 August 2007 04:30 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
with a group by you can find them:
SQL> WITH yourtable AS
  2  (
  3    SELECT 1 emp_id, 'A_0001' emp_no FROM dual UNION ALL
  4    SELECT 1 emp_id, 'A_0001' emp_no FROM dual UNION ALL
  5    SELECT 2 emp_id, 'B_0001' emp_no FROM dual UNION ALL
  6    SELECT 3 emp_id, 'C_0001' emp_no FROM dual UNION ALL
  7    SELECT 4 emp_id, 'D_0001' emp_no FROM dual UNION ALL
  8    SELECT 2 emp_id, 'B_0001' emp_no FROM dual UNION ALL
  9    SELECT 9 emp_id, 'A_0001' emp_no FROM dual
 10  )
 11  SELECT DISTINCT
 12         e.emp_id
 13       , e.emp_no
 14  FROM   yourtable e
 15  WHERE  e.emp_no IN ( SELECT emp_no
 16                       FROM   yourtable
 17                       GROUP BY emp_no HAVING COUNT(DISTINCT emp_id) > 1
 18                     )
 19  /

    EMP_ID EMP_NO
---------- ------
         9 A_0001
         1 A_0001


MHE
Re: querying exception records [message #260532 is a reply to message #260528] Mon, 20 August 2007 04:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Start with:
select EMPLOYEE_NUMBER
from tableMaster 
group by EMPLOYEE_NUMBER
having count(distinct ID) > 1
/


Ooops! Maarten was faster than me.

Regards
Michel

[Updated on: Mon, 20 August 2007 04:33]

Report message to a moderator

Re: querying exception records [message #260533 is a reply to message #260528] Mon, 20 August 2007 04:33 Go to previous messageGo to next message
Amersfoort
Messages: 3
Registered: August 2007
Junior Member
This will give you the employee numbers which have more then 1 ID
SELECT   EMPLOYEE_NUMBER
FROM     tableMaster
GROUP BY EMPLOYEE_NUMBER
HAVING   COUNT (DISTINCT ID) > 1



Edit: posted too late

[Updated on: Mon, 20 August 2007 04:34]

Report message to a moderator

Re: querying exception records [message #260608 is a reply to message #260528] Mon, 20 August 2007 07:58 Go to previous messageGo to next message
sandhyaa
Messages: 79
Registered: February 2007
Member
Thanks to all.

I tried something like this and it worked. Please share your thoughts on this.


SELECT ID, COUNT(*) FROM(
SELECT ID, EMPLOYEE_NUMBER, COUNT(*) AS CNT FROM
tableMaster GROUP BY ID, EMPLOYEE_NUMBER)
GROUP BY ID HAVING COUNT(*) > 1



Thanks
Sandi
Re: querying exception records [message #260616 is a reply to message #260608] Mon, 20 August 2007 08:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What did you get when you tried?

Regards
Michel

[Updated on: Mon, 20 August 2007 08:19]

Report message to a moderator

Re: querying exception records [message #260649 is a reply to message #260616] Mon, 20 August 2007 10:43 Go to previous messageGo to next message
sandhyaa
Messages: 79
Registered: February 2007
Member
I got ID 1 and count as 2

[Updated on: Mon, 20 August 2007 10:44]

Report message to a moderator

Re: querying exception records [message #260650 is a reply to message #260616] Mon, 20 August 2007 10:49 Go to previous messageGo to next message
sandhyaa
Messages: 79
Registered: February 2007
Member
I am sorry the query would be

SELECT EMPLOYEE_NUMBER, COUNT(*) FROM(
SELECT ID, EMPLOYEE_NUMBER, COUNT(*) AS CNT FROM
tableMaster GROUP BY ID, EMPLOYEE_NUMBER
)
GROUP BY EMPLOYEE_NUMBER HAVING COUNT(*) > 1


and I got the employee_number as A_0001

[Updated on: Mon, 20 August 2007 10:50]

Report message to a moderator

Re: querying exception records [message #260652 is a reply to message #260650] Mon, 20 August 2007 11:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It works but you used 2 groups when the other queries only used 1.

Regards
Michel
Re: querying exception records [message #260680 is a reply to message #260652] Mon, 20 August 2007 12:37 Go to previous message
sandhyaa
Messages: 79
Registered: February 2007
Member
Thanks Michel,
You are right.
Previous Topic: Creating a Calendar (merged 3 topics)
Next Topic: password encryption
Goto Forum:
  


Current Time: Sat Dec 10 20:50:31 CST 2016

Total time taken to generate the page: 0.17704 seconds