Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Left Outer Join using Fetch first 1 row

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Thu May 06, 2010 6:05 pm    Post subject: Left Outer Join using Fetch first 1 row
Reply with quote

Hi All,

I need help to solve one of my problem

I have three tables say A,B and C. I want to fetch data A and B tables, at the same time need to eliminate records depending on data in Table C.

Primary key for Table A is A1
Primary key for Table B is B1
Primary key of table C is combination of C1,C2
There can multiple rows in A which correspond to B1 in table B

My query is as below

Code:
Select A.A1
        ,B.B1
 FROM A,
         B
        left outer join C
      on B.B1 = C.C1
   AND  C2 <> 'X'
WHERE A.A1=B.B1

Here I get the output as

Code:
A1              B1
1                1
1                1
2                1
2                1

My requirement is to get unique combination of A1 and B1
This is because my table C data is as below

Code:
C1            C2
1               A
1               B
1               X

So that I am getting multiple rows. I am not getting how to eliminate duplicate rows

Is this accomplished using Fetch First 1 row by any chance?

I will be very thankful if anyone help me out in this

Regards,
Chandan
Back to top
View user's profile Send private message

chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Thu May 06, 2010 6:38 pm    Post subject:
Reply with quote

Hi Guys,

Sorry for this question I got the solution for this issue which is as below
Select A.A1
A.B1 --->(This is table A record which corresponds to B1 from Table B)
FROM A
WHERE
A.B1 in
(
Select B.B1
FROM B
left outer join C
on B.B1 = C.C1
AND C2 <> 'X'
)

Also please let me know if there is any alternate way

Regards,
Chandan
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu May 06, 2010 8:56 pm    Post subject:
Reply with quote

I don't think your answer is correct :
your query will return all rows of A1 for which a row exists in B1 .
your left join with C doesn't do anything.

I don't understand the requirement exactly :
you want rows where A and B exist, but then ?
1) C does not exist => in output : Yes or no ?
2) C exists only 1 C2 = 'X' => in output : Yes or no ?
3) C exists only 1 C2 <> 'X' => in output : Yes or no ?
4) C exists 2 rows : one = 'X', other <> 'X' => in output : Yes or no ?
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Sat May 08, 2010 9:15 am    Post subject:
Reply with quote

Hi GuyC..

Thanks for reply..

After going through your reply I also got to know my mistake and I am sorry I should have analyzed my query output before posting it..

I am quite confused with your questions..
My requirement is I want to eliminate records from results if there is a row present in C with C2 = 'X';there is chances that same C1 having more rows with C2<> 'X'

with my answered query it will give wrong output because my inner query left outer join will fetch C rows with C2<> 'X';but there is possibility of having same C1 having row with C2= 'X'

Now I could see tow options to get this done

Option 1:

Select A.A1
,B.B1
FROM A,
B
left outer join C
on B.B1 = C.C1
AND C2 = 'X'
WHERE A.A1=B.B1

After execution check the value of C2 and if it's 'X';skip the processing and go for next record

Option 2:

Select A.A1
A.B1 --->(This is table A record which corresponds to B1 from Table B)
FROM A ,B
WHERE
A.A1=B,B1
A.B1 not in
(
Select B.B1
FROM B
left outer join C
on B.B1 = C.C1
AND C2 = 'X'
)

As per my understanding Option 1 will have better performance

Please let me if I am missing something..

Regards,
Chandan
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Sat May 08, 2010 1:46 pm    Post subject:
Reply with quote

Quote:
As per my understanding Option 1 will have better performance


you can only know this after performing an EXPLAIN on your SQL.

does not make much sense to theorize over sql that does not provide the results that you want.

as far as your requirements, based on your second post,
you do not want any a1,b1 where there exists a c2=x.

why select b1 if it must equal a1?

Option 1 and 2 will not provide you with anything except C2=X, so that sql is useless.

you apparently have a cursor for a and b.

if you want to remove duplicate a1 , suggest you look at distinct.
or group by.
depending on table sizes, you might be better off using a cursor on a,b,
with either distinct or group by to drop duplicate
- you will have to look at an EXPLAIN to see which is better -
and then do a singleton against c to determine if a b1=c1 and exists (c1 =b1 and c2=x)
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Sat May 08, 2010 6:46 pm    Post subject:
Reply with quote

Hi Dick,

Thanks for your reply..

Option 1 will give both rows with C2='X' as well as C2<> 'X' as because of left outer join only in later case C2 will be null..ya I need to have a cursor for this query and select C2 also in Select clause, Check value of C2 if it's X dont process the record..I hope I am not missing anything here..

Option 2 : I mentioned the wrong query again my apologies..it's a copy paste mistake..
I am giving the corrected queries again Sorry for the typos..

Option 1: it will be a cursor Check value of C2 , if it's X skip the processing

Select A.A1
,A.A2 --->(This is table A record which corresponds to B1 from Table B)
,C,C2
FROM A,
B
left outer join C
on B.B1 = C.C1
AND C2 = 'X'
WHERE A.A2=B.B1


Option 2: Not in clause will eliminate the unwanted records as C will have only one row with C2 = 'X' for given C1 (which corresponds to B1 in B)

Select A.A1
A.A2--->(This is table A record which corresponds to B1 from Table B)
FROM A ,B
WHERE
A.A2=B.B1
A.A2 not in
(
Select B.B1
FROM B , C
where B.B1 = C.C1
AND C2 = 'X'
)

Dick, I will be very thankful if you let me know how to perform EXPLAIN on these queries and differentiate the performance..

Kindly let me know if I missing anything

Regards,
Chandan
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Sat May 08, 2010 6:58 pm    Post subject:
Reply with quote

EXPLAIN

Included in this page are links to other subjects (e.g. plan table)
for which you may or may not need to familiarize yourself.

If you are not using vsn 8, then this link will guide you to your version.

sql guide for each vsn has a write-up and explanation on how to use EXPLAIN
and set-up your own PLAN table, which you have to do,
if you are going to generate EXPLAIN for your SQL thru SPUFI.

If, in your compile/link/bind jcl, the bind parm for EXPLAIN is yes, then you can look at the associated PLAN table populated by your compilation process.

the explain output can be viewed easily in SPUFI; either your own PLAN table,
or determine the proper qualifier (from your bind output) for the sub-system EXPLAIN generated by your compile process.
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Sat May 08, 2010 7:57 pm    Post subject:
Reply with quote

Thanks for the info Dick..

Regards,
Chandan
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Mon May 10, 2010 1:27 pm    Post subject:
Reply with quote

Code:
Select A.A1 , A.B1 FROM A
WHERE not exists
   ( Select 1 FROM B  join C on B.B2 = C.C1
      where A.B1 = b.B1 AND C2 = 'X' )
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 Fetch Previous month & year in MM... girishb2 DFSORT/ICETOOL 3 Thu Sep 21, 2017 9:54 pm
No new posts Join records from 2 files with No Dup... Poha Eater DFSORT/ICETOOL 22 Sun Aug 27, 2017 10:35 pm
No new posts Reduce CPU Times for Join Sort santoshn SYNCSORT 12 Sat Jun 10, 2017 1:40 pm
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
No new posts Join giving more records than expected Danielle.Filteau SYNCSORT 2 Thu Mar 09, 2017 3:28 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us