IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Finding Duplicate patterns in the Table.


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Load new table with Old unload - DB2 DB2 6
No new posts Duplicate transid's declared using CEDA CICS 3
No new posts Pulling a fixed number of records fro... DB2 2
No new posts VB to FB - Finding LRECL SYNCSORT 4
No new posts Multiple table unload using INZUTILB DB2 2
Search our Forums:

Back to Top