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
 

 

Subselect within a Left Outer Join

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Subselect within a Left Outer Join
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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

Senior Member


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

PostPosted: Mon May 16, 2011 8:17 pm    Post subject:
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    Post subject:
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

Senior Member


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

PostPosted: Mon May 16, 2011 9:21 pm    Post subject:
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: 1278
Location: Belgium

PostPosted: Tue May 17, 2011 2:16 pm    Post subject:
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    Post subject:
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

Senior Member


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

PostPosted: Tue May 17, 2011 4:05 pm    Post subject:
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    Post subject:
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

Senior Member


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

PostPosted: Tue May 17, 2011 5:54 pm    Post subject:
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    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 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 Join key - Populate Zeros when Unpair... rexx77 DFSORT/ICETOOL 6 Thu May 12, 2016 12:22 am
No new posts Join Keys and DB2 Query jackare SYNCSORT 2 Sat Feb 13, 2016 5:57 am
No new posts Join Keys to compare two files senthamizh DFSORT/ICETOOL 2 Fri Feb 05, 2016 8:28 am


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