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
 

 

Retrieving Rows with same field having different values !

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Retrieving Rows with same field having different values !
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

Global Moderator


Joined: 14 Mar 2007
Posts: 10210
Location: italy

PostPosted: Fri Dec 12, 2008 10:06 pm    Post subject: Reply to: Retrieving Rows with same field having different v
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    Post subject:
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    Post subject: Reply to: Retrieving Rows with same field having different v
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: 2180
Location: @my desk

PostPosted: Sat Dec 13, 2008 1:04 am    Post subject:
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    Post subject:
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: 1013
Location: India

PostPosted: Mon Dec 15, 2008 12:16 pm    Post subject:
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    Post subject: Reply to: Retrieving Rows with same field having different v
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    Post subject: :(
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    Post subject:
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    Post subject: Reply to: Retrieving Rows with same field having different v
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    Post subject: Reply to: Retrieving Rows with same field having different v
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    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 INVALID FIELD OR CONSTANT IN SORTOF ?? Ron Klop DFSORT/ICETOOL 8 Wed Jan 11, 2017 3:44 pm
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
No new posts Performing arithmetic on input field zh_lad DFSORT/ICETOOL 31 Tue Dec 06, 2016 8:04 pm
No new posts Add PD field from 2nd file to PD in 1st Sushant Garje DFSORT/ICETOOL 6 Thu Dec 01, 2016 4:32 pm


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