Posted: Wed Aug 03, 2011 8:57 am Post subject: Need a query to get the results for a join
I have data in Table A as below:
Code:
SELECT * FROM YADAV.A;
---------+---------+---------+---------+---------+---------+---------+---------+
PROGNAME QUERYNO ACCESSTYPE MATCHCOLS
---------+---------+---------+---------+---------+---------+---------+---------+
POPLAO 4299 I 1
POPLAO 4299 0
IAPC010 4299 0
POPLAO 4299 I 1
POPLAO 4299 0
FPS02FL 4299 I 1
FPS02FL 4299 I 2
FPS02FL 4299 0
RUPCUC14 4299 0
RECCP05 6382 I 1
RECCP05 6382 I 1
RECCP05 6382 0
RECCP05 6382 R 0
I have data in Table B as below:
Code:
SELECT * FROM YADAV.B;
---------+---------+---------+---------+---------+---------+---------+---------+
PROGNAME QUERYNO ACCESSTYPE MATCHCOLS
---------+---------+---------+---------+---------+---------+---------+---------+
POPLAO 4299 R 0
POPLAO 4299 0
IAPC010 4299 0
POPLAO 4299 R 0
POPLAO 4299 0
FPS02FL 4299 I 1
FPS02FL 4299 I 3
FPS02FL 4299 0
RUPCUC14 4299 0
RECCP05 6382 I 1
RECCP05 6382 I 1
RECCP05 6382 0
RECCP05 6382 R 0
I am trying to make a join query to find out which have changed from I to R with same Progname and Queryno
Code:
SELECT
O.PROGNAME,
O.QUERYNO,
O.ACCESSTYPE,
N.QUERYNO,
N.ACCESSTYPE,
O.MATCHCOLS,
N.MATCHCOLS
FROM
YADAV.A O,
YADAV.B N
WHERE
(
O.PROGNAME = N.PROGNAME AND
O.QUERYNO = N.QUERYNO
) AND
O.ACCESSTYPE = 'I' AND
N.ACCESSTYPE = 'R';
I get the result as below , now I want to remove duplicates from the output as you see first 4 rows are the same , and next 2 rows are the same.
Code:
PROGNAME QUERYNO ACCESSTYPE QUERYNO ACCESSTYPE MATCHCOLS MATCHCOLS
---------+---------+---------+---------+---------+---------+---------+---------+
POPLAO 4299 I 4299 R 1 0
POPLAO 4299 I 4299 R 1 0
POPLAO 4299 I 4299 R 1 0
POPLAO 4299 I 4299 R 1 0
RECCP05 6382 I 6382 R 1 0
RECCP05 6382 I 6382 R 1 0
I need output as :
Code:
PROGNAME QUERYNO ACCESSTYPE QUERYNO ACCESSTYPE MATCHCOLS MATCHCOLS
---------+---------+---------+---------+---------+---------+---------+---------+
POPLAO 4299 I 4299 R 1 0
RECCP05 6382 I 6382 R 1 0
Basically I want to remove duplicates for Same Progname and Same Queryno.
Joined: 26 Nov 2002 Posts: 12378 Topics: 75 Location: San Jose
Posted: Wed Aug 03, 2011 12:56 pm Post subject:
yadav2005,
Alternatively you can try this untested SQL. Please code your sql's that are readable and easy to understand.
Code:
SELECT DISTINCT
A.PROGNAME
,A.QUERYNO
,A.ACCESSTYPE
,B.QUERYNO
,B.ACCESSTYPE
,A.MATCHCOLS
,B.MATCHCOLS
FROM YADAV.A A
,YADAV.B B
WHERE (A.PROGNAME,A.QUERYNO) = (B.PROGNAME,B.QUERYNO)
AND A.ACCESSTYPE = 'I'
AND B.ACCESSTYPE = 'R'
;
Thanks so much for your query and I was able to get my desired result.
NASCAR9,
Your query failed with -206:
Code:
DSNT408I SQLCODE = -206, ERROR: A.PROGNAME IS NOT A COLUMN OF AN INSERTED
TABLE, UPDATED TABLE, OR ANY TABLE IDENTIFIED IN A FROM CLAUSE, OR IS
NOT A COLUMN OF THE TRIGGERING TABLE OF A TRIGGER
DSNT418I SQLSTATE = 42703 SQLSTATE RETURN CODE
Joined: 08 Oct 2004 Posts: 274 Topics: 52 Location: California
Posted: Thu Aug 04, 2011 9:19 am Post subject:
yadav2005 wrote:
Kolusu,
Thanks so much for your query and I was able to get my desired result.
NASCAR9,
Your query failed with -206:
Code:
DSNT408I SQLCODE = -206, ERROR: A.PROGNAME IS NOT A COLUMN OF AN INSERTED
TABLE, UPDATED TABLE, OR ANY TABLE IDENTIFIED IN A FROM CLAUSE, OR IS
NOT A COLUMN OF THE TRIGGERING TABLE OF A TRIGGER
DSNT418I SQLSTATE = 42703 SQLSTATE RETURN CODE
The query should have looked like Below. I didn't notice the .A and .B at the end of your table name.
Good luck.
Code:
Select A.PROGNAME
,A.QUERYNO
,A.ACCESSTYPE
,B.QUERYNO
,B.ACCESSTYPE
,A.MATCHCOLS
,B.MATCHCOLS
FROM YADAV.A A
INNER JOIN YADAV.B B
ON A.PROGNAME = B.PROGNAME
AND A.QUERYNO = B.QUERYNO
AND B.ACCESSTYPE = 'R'
WHERE A.ACCESSTYPE = 'I'
GROUP BY A.PROGNAME
,A.QUERYNO
,A.ACCESSTYPE
,B.QUERYNO
,B.ACCESSTYPE
,A.MATCHCOLS
,B.MATCHCOLS
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
