Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Fetch combination of 2 values

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

New User


Joined: 17 Sep 2005
Posts: 49

PostPosted: Tue Jan 09, 2007 2:32 am    Post subject: Fetch combination of 2 values
Reply with quote

my reqmt is to fetch the data from the table based on the following:

table A
-------

Code:
b_num    b_cd    b_ind

54351     KLT    Y
54351     LMM    N
54351     MKL    Y

11111     BCB    Y
11111     CBG    N

42121     AFT    N
42121     GHY    Y

42222     AFT    Y
42222     GHY    Y

43432     AFT    N
43432     GHY    N
table B
-------

Code:
Code:
b_cd     b_type

KLT       A
LMM       A
MKL       A
BCB       A
CBG       C
AFT       C
GHY       A

resulted data should be like this


Code:
11111     BCB
11111     CBG
42121     AFT
42121     GHY



I want to fetch table A records where b_cd in table B should contain b_type as A and C and b_ind in table A should contain both "Y" and "N"

for example,
1.
b_num = 11111
It contains BCB and CBG in its b_cd field of table A.And the b_type in table B has A for one and c for the other.And b_ind is "Y' for one record and "N" for other record for the same b_num.
This 2 records should be fetched.

2.b_num = 54351
it contains 3 b_cd values KLT,LMM,MKL.But all b_types are same which is A. the query should not select such data whether all has A or C though it has both "Y" and "N".
i.e the records should contain b_type as both 'A' and 'C' in table b and b_ind as both 'Y' and 'N' in table A

3.b_num = 42121,AFT,GHY
It contains one b_cd as A and the other as C.And b_ind field has 'Y' and 'N'
It should be fetched.

i.e b_num record should have both A and C in b_cd field and should have both 'Y' and 'N" in b_ind field.

Hope I made the query clear.
Back to top
View user's profile Send private message

akshaiya

New User


Joined: 17 Sep 2005
Posts: 49

PostPosted: Tue Jan 09, 2007 12:14 pm    Post subject:
Reply with quote

is the above possible?
Back to top
View user's profile Send private message
DavidatK

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Tue Jan 09, 2007 11:01 pm    Post subject: Re: Fetch combination of 2 values
Reply with quote

Hello akshaiya,

Yes, your query is absolutely possible.

Try this: (you will have to change the table tables)

Code:

SELECT TTA1.B_NUM,                                       
       TTA1.B_CD                                         
  FROM SESSION.TEMP_TBL_A        TTA1                   
 WHERE EXISTS                                           
       (SELECT COUNT(DISTINCT TTA2.B_IND)               
          FROM SESSION.TEMP_TBL_A         TTA2           
         WHERE TTA2.B_NUM               = TTA1.B_NUM     
           AND TTA2.B_IND              IN ('Y', 'N')     
        HAVING COUNT(DISTINCT TTA2.B_IND) = 2           
        )                                               
   AND EXISTS                                           
       (SELECT COUNT(DISTINCT TTB2.B_TYPE)               
          FROM SESSION.TEMP_TBL_A         TTA2,         
               SESSION.TEMP_TBL_B         TTB2           
         WHERE TTA2.B_NUM               = TTA1.B_NUM     
           AND TTB2.B_CD                = TTA2.B_CD     
           AND TTB2.B_TYPE             IN ('A', 'C')     
        HAVING COUNT(DISTINCT TTB2.B_TYPE) = 2           
        )                                               
;                                                       


This is one of a couple of solutions I thought of. I think this is the easiest to understand.
Back to top
View user's profile Send private message
akshaiya

New User


Joined: 17 Sep 2005
Posts: 49

PostPosted: Wed Jan 10, 2007 5:16 am    Post subject:
Reply with quote

Thanks Dave.
But it throws me the error

Code:
DSNT408I SQLCODE = -120, ERROR:  A WHERE CLAUSE, SET CLAUSE, VALUES CLAUSE, OR
         A,SET,HOST-VARIABLE STATEMENT INCLUDES A COLUMN FUNCTION
DSNT418I SQLSTATE   = 42903 SQLSTATE RETURN CODE
DSNT415I SQLERRP    = DSNXOOS1 SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD    = -60  0  0  -1  0  0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD    = X'FFFFFFC4'  X'00000000'  X'00000000'  X'FFFFFFFF'
         X'00000000'  X'00000000' SQL DIAGNOSTIC INFORMATION
Back to top
View user's profile Send private message
DavidatK

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Wed Jan 10, 2007 5:24 am    Post subject: Re: Fetch combination of 2 values
Reply with quote

What version of DB2 are you using? I am using DB2 v7

Can you post the exact SQL, as you ran it?
Back to top
View user's profile Send private message
akshaiya

New User


Joined: 17 Sep 2005
Posts: 49

PostPosted: Wed Jan 10, 2007 5:34 am    Post subject:
Reply with quote

Its my fault.I just copy pasted the sql and changed the tb names now.It works well.

Thanks Dave! Thanks for the reply.
Back to top
View user's profile Send private message
DavidatK

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Wed Jan 10, 2007 5:41 am    Post subject: Re: Fetch combination of 2 values
Reply with quote

akshaiya,

Glad it worked out,

Hope to hear from you again icon_smile.gif icon_wink.gif
Back to top
View user's profile Send private message
akshaiya

New User


Joined: 17 Sep 2005
Posts: 49

PostPosted: Wed Jan 10, 2007 5:43 am    Post subject:
Reply with quote

Quote:
This is one of a couple of solutions I thought of. I think this is the easiest to understand.


Just in curiosity,wanted to know the second solution,if possible.
Back to top
View user's profile Send private message
priyesh.agrawal

Senior Member


Joined: 28 Mar 2005
Posts: 1452
Location: Chicago, IL

PostPosted: Wed Jan 10, 2007 10:14 am    Post subject: Re: Fetch combination of 2 values
Reply with quote

My idea to think on this:-

If we put a join on these tables to combine and get something like this:-

Code:
54351     KLT    Y    A
54351     LMM    N    A
54351     MKL    Y    A

11111     BCB    Y    A
11111     CBG    N    C

42121     AFT    N    C
42121     GHY    Y    A

42222     AFT    Y    C
42222     GHY    Y    A

43432     AFT    N    C
43432     GHY    N   A


And then-
Code:
SELECT DESIRED COMLUMN FROM (<JOINED TABLE>)
GROUP BY B.NUM HAVING COUNT(DISTINCT B.TYPE) >= 2 AND COUNT (DISTINCT B.IND) > 2;


As there is no value other than "A" and "C" for B_TYPE and there is no value other than "Y" and "N" for B_IND... I didn't put any cond to check value for:-

Dave... I didnt test it ... may be you can understand idea and correct me, if wrong.
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 Sorting of hex values Saurabh_mi DFSORT/ICETOOL 11 Thu May 25, 2017 3:49 pm
No new posts Replace values in the input data Vikas Maharnawar DFSORT/ICETOOL 10 Thu May 11, 2017 2:18 pm
No new posts Compare two files and subtract values ameetmund DFSORT/ICETOOL 7 Fri Mar 31, 2017 3:35 pm
No new posts Check Binary Values for symbols gandikk CA Products 3 Tue Mar 21, 2017 5:11 am
No new posts To display values of multiple rows in... vinu78 DB2 2 Thu Mar 09, 2017 1:20 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us