Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref

Author Message
rupali_shah
Warnings : 1

New User

Joined: 14 Mar 2009
Posts: 6

Posted: Tue Mar 17, 2009 12:45 pm    Post subject: To find the duplicate set of rows

WE HAVE A TABLE WITH X COLUMNS AND THE QUERY IS TO FIND THE
NUMBER OF ROWS WHICH HAVE DUPLICATE VALUES IN
THREE COLUMNS.

FOR EXAMPLE IN TABLE TAB1

WE HAVE FOUR COLUMNS
 Code: COL1      COL2   COL3  COL4 01        342    232   2112 02        342    232   2342 03        342    232   2342 04        342    441   2342

If we Want the number of rows having duplicate values in set of COL2, COL3 AND COL4, the result of the rows query should be like this:

 Code: COL1 , COL2, COL3, COL4 02     342    232  2342 03     342    232  2342

Edited: Please use BBcode when You post some code/error, that's rather readable, Thanks... Anuj

Warning: Turn Off Caps

Succor

New User

Joined: 20 Feb 2009
Posts: 96
Location: Bangalore :)

Posted: Tue Mar 17, 2009 3:17 pm    Post subject: Reply to: TO FIND THE DUPLICATE SET OF ROWS

Rupali,
Try the following Query and let us know if suits your requirement.
 Code: SELECT DISTINCT X.A,X.B,X.C,X.D                              FROM EMP X ,(SELECT A,B,C,D FROM EMP ) AS Y WHERE X.B = Y.B                                              AND   X.C = Y.C                                              AND   X.D = Y.D                                              AND   X.A <> Y.A ;

Replace EMP with your table name and A,B,C,D with your respective column names.
WTF
Anuj Dhawan

Senior Member

Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

Posted: Tue Mar 17, 2009 3:17 pm    Post subject:

Hi,

If I understood correctly, try something like:
 Code: select col2,col3, col4, count(*) from table group by col2,col3, col4 having count(*) > 1
senthilnathanj

New User

Joined: 31 Jul 2007
Posts: 47
Location: chennai

Posted: Tue Mar 17, 2009 5:10 pm    Post subject:

Hi rupali,
Try the following query and let us know...
 Code: WITH TEMP(COL1,COL2) AS ( SELECT COL1,COL2 FROM TAB1 GROUP BY COL1,COL2 HAVING COUNT(*) > 2) SELECT A.COL1,A.COL2 FROM TAB1 A WHERE A.COL1 IN (SELECT T.COL1 FROM TEMP T) AND A.COL2 IN (SELECT T.COL2 FROM TEMP T);

u can get the extract output..
gkanswa

New User

Joined: 09 Feb 2009
Posts: 5
Location: Pune

Posted: Thu Mar 19, 2009 5:43 pm    Post subject:

Hi Rupali,

You can try this one -

 Code: Select * from TAB1 A where 2 <= (select count (distinct Col2) from TAB1                    where COL2= A.COL2  and                              COL3 = A.COL3 and                              COL4= A.COL4);
"Code"d

I don't have mainframe access, so couldn't test it.
Please do correct me if I am wrong.

Girish
 All times are GMT + 6 Hours
 Page 1 of 1

Search our Forum:

 Topic Author Forum Replies Posted Similar Topics RACF- How to find the Last access of ... rahul shanmuganatan All Other Mainframe Topics 6 Thu Jun 21, 2018 3:19 pm Rexx code to find greater then symobl... Bharath Vikraman CLIST & REXX 8 Tue May 29, 2018 9:40 am Splitting one row into multiple Rows ... Rajan Moorthy DFSORT/ICETOOL 9 Mon May 14, 2018 10:58 pm Duplicate PARM on OUTFIL sancraig16 SYNCSORT 11 Thu Apr 05, 2018 10:25 pm Find & Replace string in CA-SORT mrgnndhmk CA Products 1 Fri Mar 30, 2018 12:58 am

 © 2003-2017 IBM MAINFRAME Software Support Division
 Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us