View previous topic :: View next topic
|
Author |
Message |
ramsri
Active User
Joined: 18 Oct 2008 Posts: 380 Location: India
|
|
|
|
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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10886 Location: italy
|
|
|
|
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 |
|
|
Bharath Bhat
Active User
Joined: 20 Mar 2008 Posts: 283 Location: chennai
|
|
|
|
Even the IN should have worked, provided it was written in a proper way.
Can you please post your SQL? |
|
Back to top |
|
|
ramsri
Active User
Joined: 18 Oct 2008 Posts: 380 Location: India
|
|
|
|
Here is what I tried...........
Code: |
SELECT * FROM DBA1.A1_TABLE
WHERE A1_CODE IN ('X1A0','X1A1')
WITH UR;
|
Thanks. |
|
Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
|
|
|
|
And what did you get in output? |
|
Back to top |
|
|
Bharath Bhat
Active User
Joined: 20 Mar 2008 Posts: 283 Location: chennai
|
|
|
|
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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
Try with LIKE, might have spaces padded.
Sushanth |
|
Back to top |
|
|
atosvv
New User
Joined: 04 Dec 2008 Posts: 11 Location: Mumbai
|
|
|
|
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 |
|
|
ramsri
Active User
Joined: 18 Oct 2008 Posts: 380 Location: India
|
|
|
|
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 |
|
|
ramsri
Active User
Joined: 18 Oct 2008 Posts: 380 Location: India
|
|
|
|
Hi,
Please ignore my previous post.......copy/paste error
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 |
|
|
atosvv
New User
Joined: 04 Dec 2008 Posts: 11 Location: Mumbai
|
|
|
|
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 |
|
|
ramsri
Active User
Joined: 18 Oct 2008 Posts: 380 Location: India
|
|
|
|
VV, I will try your query and inform. Thanks for help. |
|
Back to top |
|
|
|