|
View previous topic :: View next topic
|
| Author |
Message |
akshaiya
New User
Joined: 17 Sep 2005
Posts: 49
|
|
|
|
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 |
|
 |
akshaiya
New User
Joined: 17 Sep 2005
Posts: 49
|
|
|
|
| is the above possible? |
|
| Back to top |
|
|
DavidatK
Active Member
Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA
|
|
|
|
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 |
|
|
akshaiya
New User
Joined: 17 Sep 2005
Posts: 49
|
|
|
|
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 |
|
|
DavidatK
Active Member
Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA
|
|
|
|
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 |
|
|
akshaiya
New User
Joined: 17 Sep 2005
Posts: 49
|
|
|
|
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 |
|
|
DavidatK
Active Member
Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA
|
|
|
|
akshaiya,
Glad it worked out,
Hope to hear from you again   |
|
| Back to top |
|
|
akshaiya
New User
Joined: 17 Sep 2005
Posts: 49
|
|
|
|
| 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 |
|
|
priyesh.agrawal
Senior Member
Joined: 28 Mar 2005
Posts: 1448
Location: Chicago, IL
|
|
|
|
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 |
|
|
|
|
