Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Finding Duplicate patterns in the Table.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Raghuraman_New

New User


Joined: 03 Oct 2008
Posts: 43
Location: Chennai,India.

PostPosted: Wed Sep 15, 2010 9:54 pm    Post subject: Finding Duplicate patterns in the Table.
Reply with quote

Hi ,
My requirement is to fetch the rows(data) from the Table having simillar Patters of data's in the same table itself.

Example : I am having the table with 2 coloumn C1 and C2,

Code:

----------------------
C1            C2
----------------------

U1             A
U1             B
U1             C
U2             E
U2             F
U3             A
U3             B
U3             C
U3             D
U4             A
U4             B
U4             C
U5             E
U5             F
U6             D
U6             E
U7             A
U7             B
U7             C

Required Output :

Code:

-------------------------
C1                C2
-----------------------

U1                U4
U1                U7
U2                U5
U4                U1
U5                U2
U7                U1


Query Rule :

The U1,U4,U7 are having the same kind of C2 values (A,B,C) and

U2, U5 having the same kind of C2 values (E,F).

Important : U1 and U3 having (A,B,C) as common C2 values but U3 has one more C2 value D. These Combinations are not valid . I need the exact matching one.


Note :

I have tried for single input case and got output but i want for general case like above. Please let me know if you have any questions or concerns.

Our Shop is having DB2 z/OS V8 only.
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu Sep 16, 2010 1:25 pm    Post subject:
Reply with quote

Code:
Select a.C1 , b.c1 from TAB1 a , TAB1 b                       
where a.c2 = b.c2                                                       -- SELF JOIN
and a.c1 <> b.c1                                                        -- make sure we don't compare U1 with U1
and a.c1 = (select min(a2.c1) from TAB1 a2 where  a2.c2 = a.c2)         -- to eliminate output U4=U1, U4=U7 when u1=u4 u1=u7
and a.c2 = (select min(a2.c2) from TAB1 a2 where  a2.c1 = a.c1)         -- to avoid checking U1 3 times (for each value of c2)

 and not exists (                                                       
        select c2 from TAB1 C 
        where c.c1 = a.c1                                               -- All C2 for U1
          and not exists (                                              --  Can't find
               select c2 from TAB1 d where d.c1 = b.c1 and d.c2 = c.c2) --   the same C2 in U4
               )
 and not exists (                                                       
        select c2 from TAB1 C                                           
        where c.c1 = b.c1                                               -- All C2 for U4
          and not exists (                                              --  Can't find
               select c2 from TAB1 d where d.c1 = a.c1 and d.c2 = c.c2) --    the same C2 in U1
               )
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts LMINIT problem - not finding DDNAME Danielle.Filteau CLIST & REXX 7 Tue Sep 19, 2017 9:57 pm
No new posts Insert a Row_number into table useit DB2 2 Tue Sep 19, 2017 1:07 pm
No new posts how to see when the last read access ... Mike 1304 DB2 1 Tue Sep 12, 2017 7:52 pm
No new posts PC (UTF-8) -> z/OS (EBCDIC) -> ... prino All Other Mainframe Topics 4 Fri Sep 01, 2017 1:47 am
No new posts Finding IMS resource blocker after se... Hooman24 IMS DB/DC 1 Wed Aug 16, 2017 9:17 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us