IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Subselect within a Left Outer Join


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
cvishu

Active User


Joined: 31 Jul 2007
Posts: 136
Location: india

PostPosted: Mon May 16, 2011 5:49 pm
Reply with quote

Hi am trying to execute the below query
Code:

SELECT A.IDN_RECON_TASK,B.IDN_RECON_TASK_TO
 FROM TABL1 A
 LEFT OUTER JOIN TABL2 B ON
B.IDN_RECON_TASK_FROM = A.IDN_RECON_TASK AND
         (  'MTCH' =
           ( SELECT SUBSTR(C.TXT_RECON_ACT_DESC,2,4)
              FROM TABL3 C
               WHERE
              C.IDN_RECON_TASK = B.IDN_RECON_TASK_TO ))
WHERE
A.KEY_ACCT_HS ='XXXXXXX' AND
DT2_RECON_TASK = 'YYYYYY'



its giving me an error " Invalid use of the ON clause."

Could any one please advice
Back to top
View user's profile Send private message
rocky_balboa

New User


Joined: 24 Mar 2010
Posts: 61
Location: Cape of Good Hope

PostPosted: Mon May 16, 2011 6:59 pm
Reply with quote

Outer joins cannot contain subqueries in the join condition.
Back to top
View user's profile Send private message
cvishu

Active User


Joined: 31 Jul 2007
Posts: 136
Location: india

PostPosted: Mon May 16, 2011 7:16 pm
Reply with quote

Rocky , is there any other way that we can achive the above query ?

without subselects ?
Back to top
View user's profile Send private message
rocky_balboa

New User


Joined: 24 Mar 2010
Posts: 61
Location: Cape of Good Hope

PostPosted: Mon May 16, 2011 7:38 pm
Reply with quote

cvishu

Just a quick thought,
Create a CTE using the conditions in your subquery and then join it with rest of the tables. It should work.
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Mon May 16, 2011 7:41 pm
Reply with quote

Hi Rocky...

Can you please tell me what is CTE?
Back to top
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India

PostPosted: Mon May 16, 2011 8:17 pm
Reply with quote

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

Make manuals your friend. (Best thing about them is - they talk what they mean!)
Back to top
View user's profile Send private message
rocky_balboa

New User


Joined: 24 Mar 2010
Posts: 61
Location: Cape of Good Hope

PostPosted: Mon May 16, 2011 9:15 pm
Reply with quote

Thanks Anuj for such crisp explanation. Hopefully I will not have to expand CTEs again. icon_smile.gif
Back to top
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India

PostPosted: Mon May 16, 2011 9:21 pm
Reply with quote

rocky_balboa wrote:
Thanks Anuj for such crisp explanation. Hopefully I will not have to expand CTEs again. icon_smile.gif
You're welcome. I saw your comment on the same subject, in other topic, and thought to chip-in in this thread...icon_smile.gif

Have a nice stay here...icon_smile.gif
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Tue May 17, 2011 2:16 pm
Reply with quote

if you need additional where clauses that are not allowed in the ON-clause
you should use a Subselect or CTE

additionally a Like is faster than a SUBSTR


not tested:
Code:
SELECT
A.IDN_RECON_TASK
,B.IDN_RECON_TASK_TO
FROM TABL1 A
LEFT OUTER JOIN
(select IDN_RECON_TASK_TO from TABL2 B2
where exists (select 1 from TABL3 C
             where C.IDN_RECON_TASK = B2.IDN_RECON_TASK_TO 
               and C.TXT_RECON_ACT_DESC like '__MTCH%') B
ON B.IDN_RECON_TASK_FROM = A.IDN_RECON_TASK

WHERE A.KEY_ACCT_HS ='XXXXXXX'
  AND DT2_RECON_TASK = 'YYYYYY'
Back to top
View user's profile Send private message
rakesh1155

New User


Joined: 21 Jan 2009
Posts: 84
Location: India

PostPosted: Tue May 17, 2011 3:53 pm
Reply with quote

Quote:
Make manuals your friend. (Best thing about them is - they talk what they mean!)


I am in the process of making the manuals my friends ;) icon_smile.gif

And I agree they do talk what they mean! And when we dont understand what they mean then we take help from the experts. icon_smile.gif
Back to top
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India

PostPosted: Tue May 17, 2011 4:05 pm
Reply with quote

rakesh1155 - You're not the OP and also don't seem to be lurking around this thread, however, it looks nice to see you guys read what we put in here, even in fine-print! icon_biggrin.gif
Back to top
View user's profile Send private message
rakesh1155

New User


Joined: 21 Jan 2009
Posts: 84
Location: India

PostPosted: Tue May 17, 2011 5:00 pm
Reply with quote

I have always been wondering what does OP stand for?
Is it 'original poster' ?

By the way, I do follow the posts on ibmmainframes.com. Some I have immediate answers for... Some I dont... Just that you guys are too fast icon_smile.gif

And am still learning to understand the broader view of looking at the original query. icon_smile.gif
Back to top
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India

PostPosted: Tue May 17, 2011 5:54 pm
Reply with quote

rakesh1155 wrote:
I have always been wondering what does OP stand for?
Is it 'original poster' ?
Yup, that's correct! icon_smile.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Shift left VB record without x00 endi... DFSORT/ICETOOL 11
No new posts Join multiple records using splice DFSORT/ICETOOL 5
No new posts Join 2 files according to one key field. JCL & VSAM 3
No new posts JOIN STATEMENT PERFORMANCE. DFSORT/ICETOOL 12
No new posts Join files where value in one is betw... DFSORT/ICETOOL 6
Search our Forums:

Back to Top