View previous topic :: View next topic
|
Author |
Message |
cvishu
Active User
Joined: 31 Jul 2007 Posts: 136 Location: india
|
|
|
|
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 |
|
|
rocky_balboa
New User
Joined: 24 Mar 2010 Posts: 61 Location: Cape of Good Hope
|
|
|
|
Outer joins cannot contain subqueries in the join condition. |
|
Back to top |
|
|
cvishu
Active User
Joined: 31 Jul 2007 Posts: 136 Location: india
|
|
|
|
Rocky , is there any other way that we can achive the above query ?
without subselects ? |
|
Back to top |
|
|
rocky_balboa
New User
Joined: 24 Mar 2010 Posts: 61 Location: Cape of Good Hope
|
|
|
|
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 |
|
|
gylbharat
Active Member
Joined: 31 Jul 2009 Posts: 565 Location: Bangalore
|
|
|
|
Hi Rocky...
Can you please tell me what is CTE? |
|
Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6250 Location: Mumbai, India
|
|
|
|
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 |
|
|
rocky_balboa
New User
Joined: 24 Mar 2010 Posts: 61 Location: Cape of Good Hope
|
|
|
|
Thanks Anuj for such crisp explanation. Hopefully I will not have to expand CTEs again. |
|
Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6250 Location: Mumbai, India
|
|
|
|
rocky_balboa wrote: |
Thanks Anuj for such crisp explanation. Hopefully I will not have to expand CTEs again. |
You're welcome. I saw your comment on the same subject, in other topic, and thought to chip-in in this thread...
Have a nice stay here... |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
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 |
|
|
rakesh1155
New User
Joined: 21 Jan 2009 Posts: 84 Location: India
|
|
|
|
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 ;)
And I agree they do talk what they mean! And when we dont understand what they mean then we take help from the experts. |
|
Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6250 Location: Mumbai, India
|
|
|
|
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! |
|
Back to top |
|
|
rakesh1155
New User
Joined: 21 Jan 2009 Posts: 84 Location: India
|
|
|
|
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
And am still learning to understand the broader view of looking at the original query. |
|
Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6250 Location: Mumbai, India
|
|
|
|
rakesh1155 wrote: |
I have always been wondering what does OP stand for?
Is it 'original poster' ? |
Yup, that's correct! |
|
Back to top |
|
|
|