Display Duplicate & non Duplicate keys using DB2 Query



IBM's flagship relational database management system

Display Duplicate & non Duplicate keys using DB2 Query

Postby shankar_dh » Fri May 10, 2013 1:53 pm

Hi ,

I have below requirement to produce report:-
Need to check the each table for uniqueness of the primary key, If the Key is not unique need to display those keys else need to display "No Duplicates"
I found out the below Query which finds the Duplicate Keys but I want Non Duplicate Keys should be displayed with message "No Duplicates"

SELECT 'DEPOSITOR_UNIQUE_ID', --> This is the Primary key in table1(It can accept duplicate keys)
DEPOSITOR_UNIQUE_ID
FROM table1
GROUP BY DEPOSITOR_UNIQUE_ID
HAVING COUNT(*) > 1

Report should be something like below,
-----------------------------------------------------------
TableName----+----Primary Key----+------Duplicate
-----------------------------------------------------------
Table1 + Depositor unique ID + No duplicates
Table2 + Depositor unique ID + No duplicates
Table3 + Account unique ID + CDA000003010395890000000000000000
................................ + CDA000660001350416000000000000000
shankar_dh
 
Posts: 41
Joined: Fri Mar 22, 2013 1:00 pm
Has thanked: 1 time
Been thanked: 0 time

Re: Display Duplicate & non Duplicate keys using DB2 Query

 

Re: Display Duplicate & non Duplicate keys using DB2 Query

Postby pmartyn » Sat May 11, 2013 1:20 am

I don't mean to sound 'simple' but why don't you use the IF/ELSE construct available in DB2? My apologies in advance because I am not logged on right now to test the results but your SQL should look something like this:
SELECT 'DEPOSITOR_UNIQUE_ID', --> This is the Primary key in table1(It can accept duplicate keys)
DEPOSITOR_UNIQUE_ID
FROM table1
GROUP BY DEPOSITOR_UNIQUE_ID
IF COUNT(*) = 1
 THEN 'No Duplicates'
  ELSE
      DEPOSITOR_UNIQUE_ID
END IF


Please be reminded that this may not be your entire answer but it WILL point you in the right direction.

Best of luck,
pm


For more information see the link http://pic.dhe.ibm.com/infocenter/dzich ... lsqlpl.htm
pmartyn
 
Posts: 42
Joined: Thu Feb 28, 2013 7:11 pm
Has thanked: 5 times
Been thanked: 3 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post