| View previous topic :: View next topic |
| Author |
Message |
radkrish
Beginner
Joined: 12 Aug 2005
Posts: 102
Topics: 19
|
 Posted: Fri Jan 05, 2007 4:00 pm Post subject: DB2 query |
 |
|
my reqmt is to fetch the data from the table based on the following:
table A
-------
| Code: | b_num b_cd
54351 KLT
54351 LMM
54351 MKL
11111 BCB
11111 CBG
42121 AFT
42121 GHY |
table B
-------
| 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 b_num,b_cd where b_cd in table B should contain b_type asA and C .
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.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
3.b_num = 42121,AFT,GHY
It contains one b_cd as A and the other as C.It should be fetched.
i.e b_num record should have the combination of both A and C. |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12372
Topics: 75
Location: San Jose
|
| Posted: Fri Jan 05, 2007 4:46 pm Post subject: |
|
|
radkrish,
try this
| Code: |
SELECT *
FROM Tablea
WHERE B_NUM IN (SELECT B_NUM
FROM Tablea
WHERE EXISTS (SELECT B_CD
FROM Tableb
WHERE B_TYPE = 'A')
AND B_CD IN (SELECT B_CD
FROM Tableb
WHERE B_TYPE = 'C'))
;
|
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
radkrish
Beginner
Joined: 12 Aug 2005
Posts: 102
Topics: 19
|
| Posted: Fri Jan 05, 2007 5:00 pm Post subject: |
|
|
Thanks Kolusu.I am using the same query.But it is throwing the following error.
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL ".". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: ) UNION EXCEPT.
not sure where it went wrong. |
|
| Back to top |
|
|
radkrish
Beginner
Joined: 12 Aug 2005
Posts: 102
Topics: 19
|
| Posted: Fri Jan 05, 2007 5:06 pm Post subject: |
|
|
| I copied the same query changing just a the table names. |
|
| Back to top |
|
|
radkrish
Beginner
Joined: 12 Aug 2005
Posts: 102
Topics: 19
|
| Posted: Fri Jan 05, 2007 5:08 pm Post subject: |
|
|
oops.I made it wrong.
Thanks Kolusu. |
|
| Back to top |
|
|
radkrish
Beginner
Joined: 12 Aug 2005
Posts: 102
Topics: 19
|
| Posted: Fri Jan 05, 2007 5:23 pm Post subject: |
|
|
| Code: | b_num b_cd
54351 KLT
11111 BCB
42121 AFT
42121 GHY |
the query also fetches single B_cd records.
Is there any possibility by not allowing the single b_cd value for that b_num.
54351 KLT
11111 BCB
The above 2 should not be fetched. |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12372
Topics: 75
Location: San Jose
|
| Posted: Fri Jan 05, 2007 6:52 pm Post subject: |
|
|
radkrish,
I get the exact results with the same data you provided in the first post. Make sure that you copied the sql correctly
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
radkrish
Beginner
Joined: 12 Aug 2005
Posts: 102
Topics: 19
|
| Posted: Sun Jan 07, 2007 11:30 pm Post subject: |
|
|
I am querying the correct SQL.dont know why it fetches that single record.
single record should be omitted. |
|
| Back to top |
|
|
radkrish
Beginner
Joined: 12 Aug 2005
Posts: 102
Topics: 19
|
| Posted: Mon Jan 08, 2007 10:11 am Post subject: |
|
|
I filtered it thru DFSORT and got the data.Thanks.
Kolusu,
is that possible if i want to add one more condition on table A side to the existing query.
table A
-------
| Code: | b_num b_cd b_ind
54351 KLT Y
54351 LMM N
54351 MKL N
11111 BCB N
11111 CBG Y
42121 AFT Y
42121 GHY N
table B
-------
b_cd b_type
KLT A
LMM A
MKL A
BCB A
CBG C
AFT C
GHY A |
resulted data should be like this
11111 BCB
11111 CBG
42121 AFT
42121 GHY
i.e B_ind should be both Y and N for b_num key. |
|
| Back to top |
|
|
radkrish
Beginner
Joined: 12 Aug 2005
Posts: 102
Topics: 19
|
| Posted: Mon Jan 08, 2007 12:42 pm Post subject: |
|
|
I tried with another EXISTS for b_ind.but i couldnt get the combination of "Y" and "N" in table A with "A" and "C" in table B.
Is there any other option to attain b_num with combination of "Y,N"(b_ind) in table A and "A,C"(b_type) in table B?
Hope I made the query clear. |
|
| Back to top |
|
|
radkrish
Beginner
Joined: 12 Aug 2005
Posts: 102
Topics: 19
|
| Posted: Tue Jan 09, 2007 1:45 am Post subject: |
|
|
| is the above possible? can anyone pls tell me? |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
