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

Retrieving Rows with same field having different values !
IBM Mainframe Forums -> DB2
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
ramsri

Active User


Joined: 18 Oct 2008
Posts: 380
Location: India
PostPosted: Fri Dec 12, 2008 8:58 pm
 Reply with quote
Hi,

We have a DB2 table (A1_TABLE) with three fields where the data appears as follows:

Table: A1_TABLE
Code:

---------+---------+---------+---------+--
A1_KEY         A1_NBR         A1_CODE     
---------+---------+---------+---------+--
A1A            A1A000         X1A0       
A1A            A1A000         X1A1       
A1A            A1A000         X1A2       
N1Q            N1Q000         X1A0           
N1Q            N1Q000         X1A1           
N1Q            N1Q000         X1A2
A1S            A1S000         X1A0
N1H            N1H000         X1A0           
N1H            N1H000         X1A1           
N1H            N1H000         X1A2           
N1H            N1H000         X1A3
O1P            O1P000         X1A0
O1P            O1P000         X1A9


I want to select all those rows that have X1A0 & X1A1 on them. I tried using EXISTS & IN on A1_CODE field but could not succeed.

Result Table:
Code:

---------+---------+---------+---------+--
A1_KEY         A1_NBR         A1_CODE     
---------+---------+---------+---------+--
A1A            A1A000         X1A0       
A1A            A1A000         X1A1       
N1Q            N1Q000         X1A0           
N1Q            N1Q000         X1A1           
N1H            N1H000         X1A0           
N1H            N1H000         X1A1           


Would you please suggest me the SQL query for this requirement?

Thank you.
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10888
Location: italy
PostPosted: Fri Dec 12, 2008 10:06 pm
 Reply with quote
I might look dumb but what about something in the style

select ... from .... where a1_code = "x1a0" or a1_code = "x1a1"


just the idea, it' s up to You to write it in the proper way
Back to top
View user's profile Send private message
Bharath Bhat

Active User


Joined: 20 Mar 2008
Posts: 283
Location: chennai
PostPosted: Fri Dec 12, 2008 10:20 pm
 Reply with quote
Even the IN should have worked, provided it was written in a proper way.

Can you please post your SQL?
Back to top
View user's profile Send private message
ramsri

Active User


Joined: 18 Oct 2008
Posts: 380
Location: India
PostPosted: Sat Dec 13, 2008 1:01 am
 Reply with quote
Here is what I tried........... icon_rolleyes.gif

Code:

SELECT * FROM DBA1.A1_TABLE
 WHERE A1_CODE IN ('X1A0','X1A1')
WITH UR;


Thanks.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
PostPosted: Sat Dec 13, 2008 1:04 am
 Reply with quote
And what did you get in output?
Back to top
View user's profile Send private message
Bharath Bhat

Active User


Joined: 20 Mar 2008
Posts: 283
Location: chennai
PostPosted: Sat Dec 13, 2008 12:25 pm
 Reply with quote
That code should definitely work if the table has data as given in your 1st post. Check if the table has proper data and then check if you are accessing the same table.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India
PostPosted: Mon Dec 15, 2008 12:16 pm
 Reply with quote
Try with LIKE, might have spaces padded.

Sushanth
Back to top
View user's profile Send private message
atosvv

New User


Joined: 04 Dec 2008
Posts: 11
Location: Mumbai
PostPosted: Mon Dec 15, 2008 3:08 pm
 Reply with quote
Hi Ramsri,

Please execute and see the result of the below query.

Code:


SELECT * FROM A1_TABLE A WHERE A.CODE IN ('X1A1', 'X1A0')
AND (EXISTS
           ( SELECT * FROM A1_TABLE
             WHERE A.KEY = KEY
             AND A.NBR = NBR
             AND ((A.CODE = 'X1A0' AND CODE = 'X1A1') OR
                      (A.CODE = 'X1A1' AND CODE = 'X1A0'))
           )
     ) ORDER BY KEY, NBR, CODE;



Thanks.

-VV
Back to top
View user's profile Send private message
ramsri

Active User


Joined: 18 Oct 2008
Posts: 380
Location: India
PostPosted: Mon Dec 15, 2008 3:13 pm
 Reply with quote
Arun / Bharat,

with "IN ('X1A0','X1A1'), I get output like below:

Code:

---------+---------+---------+---------+--
A1_KEY         A1_NBR         A1_CODE     
---------+---------+---------+---------+--
A1A            A1A000         X1A0       
A1A            A1A000         X1A1       
N1Q            N1Q000         X1A0           
N1Q            N1Q000         X1A1           
A1S            A1S000         X1A0
N1H            N1H000         X1A0           
N1H            N1H000         X1A1           
N1H            N1H000         X1A3
O1P            O1P000         X1A0


Thanks.
Back to top
View user's profile Send private message
ramsri

Active User


Joined: 18 Oct 2008
Posts: 380
Location: India
PostPosted: Mon Dec 15, 2008 3:16 pm
 Reply with quote
Hi,

Please ignore my previous post.......copy/paste error icon_redface.gif

The below is what I got with "IN ('X1A0','X1A1').......

Code:

---------+---------+---------+---------+--
A1_KEY         A1_NBR         A1_CODE     
---------+---------+---------+---------+--
A1A            A1A000         X1A0       
A1A            A1A000         X1A1       
N1Q            N1Q000         X1A0           
N1Q            N1Q000         X1A1           
A1S            A1S000         X1A0
N1H            N1H000         X1A0           
N1H            N1H000         X1A1           
O1P            O1P000         X1A0


Thanks.
Back to top
View user's profile Send private message
atosvv

New User


Joined: 04 Dec 2008
Posts: 11
Location: Mumbai
PostPosted: Mon Dec 15, 2008 3:28 pm
 Reply with quote
Hi Ramsri,

13 records are available in A1_TABLE is
Code:

KEY   NBR   CODE
A1A   A1A000   X1A1
A1A   A1A000   X1A2
N1Q   N1Q000   X1A0
N1Q   N1Q000   X1A1
N1Q   N1Q000   X1A2
A1S   A1S000   X1A0
N1H   N1H000   X1A0
N1H   N1H000   X1A1
N1H   N1H000   X1A2
N1H   N1H000   X1A3
O1P   O1P000   X1A0
O1P   O1P000   X1A9
A1A   A1A000   X1A0


When i fire this query
Code:


SELECT * FROM A1_TABLE A WHERE A.CODE IN ('X1A1', 'X1A0') AND (EXISTS ( SELECT * FROM A1_TABLE WHERE A.KEY = KEY AND A.NBR = NBR AND ((A.CODE = 'X1A0' AND CODE = 'X1A1') OR (A.CODE = 'X1A1' AND CODE = 'X1A0')))) ORDER BY KEY, NBR, CODE;




I get the below result.

Code:


KEY   NBR   CODE
A1A   A1A000   X1A0
A1A   A1A000   X1A1
N1H   N1H000   X1A0
N1H   N1H000   X1A1
N1Q   N1Q000   X1A0
N1Q   N1Q000   X1A1



I think it matches with your expected output (as specified at your first post).

Thanks.

-VV
Back to top
View user's profile Send private message
ramsri

Active User


Joined: 18 Oct 2008
Posts: 380
Location: India
PostPosted: Mon Dec 15, 2008 4:50 pm
 Reply with quote
VV, I will try your query and inform. Thanks for help.
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 Help required to reset decimal points... DFSORT/ICETOOL 10
No new posts Sorting Date Field DFSORT/ICETOOL 4
No new posts how to eliminate null indicator value... DB2 7
No new posts Reflection 3270 screen shows only 23 ... Compuware & Other Tools 6
No new posts INCLUDE OMIT COND for Multiple values... DFSORT/ICETOOL 5
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us