Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups 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: 1278
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: 6966
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: 6966
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: 1278
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 Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
No new posts How can we have the varying lenth of ... Gunapala CN DFSORT/ICETOOL 6 Fri Oct 14, 2016 7:31 pm
No new posts Coverting PD to FS and also left just... Atul Banke DFSORT/ICETOOL 6 Wed Aug 24, 2016 4:31 pm
No new posts Multi row fetch - "for read only... Nileshkul DB2 3 Sun Aug 14, 2016 12:52 am
No new posts JCL to fetch schedule status from act... parasmalik20 CA Products 1 Thu Jun 02, 2016 7:11 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us