I would like to write following query with out using outerjoined table in subquery. This query is working fine in SQL Server,
but Oracle does not support using outer joined table in Sub query, In this case table
postedpunches can not be used in subquery. It is a big limitation of Oracle.
Oracle Show Error:a column may not be outer-joined to a subquery
Can anyone please help me out in rewriting the following in such way that it does not use outerjoined table in subquery?
SELECT count(*)
FROM postedpunches
left outerjoin paycodes ON paycodes.paycode
= postedpunches.paycode AND paycodes.effectivedate
IN(SELECTMAX(b.effectivedate) FROM paycodes b WHERE b.paycode
= paycodes.paycode AND b.effectivedate
<=postedpunches.punchdated)
Thanks for providing the links, but I dont' think so the soution provided by mufasa works as still it uses e.emp_id in subquery.
My Collegue helped me in this regard. Following is the solutions with out using outerjoined table in subquery. My Big Thanks to Vinod.
SELECT count(*)
FROM postedpunches
left outer join (select * from paycodes
where paycodes.effectivedate IN
(SELECT MAX(b.effectivedate)
FROM paycodes b, postedpunches p
WHERE b.paycode = paycodes.paycode
AND b.effectivedate <= p.punchdate))paycodes
on paycodes.paycode = postedpunches.paycode
Contributor
2350 Points
1014 Posts
Need to Rewrite the Query, Error: a column may not be outer-joined to a subquery
May 11, 2010 05:05 PM|duttavr|LINK
Hi,
I would like to write following query with out using outerjoined table in subquery. This query is working fine in SQL Server, but Oracle does not support using outer joined table in Sub query, In this case table postedpunches can not be used in subquery. It is a big limitation of Oracle.
Oracle Show Error: a column may not be outer-joined to a subquery
Can anyone please help me out in rewriting the following in such way that it does not use outerjoined table in subquery?
SELECT
count(*)
FROM postedpunches
left outer join paycodes
ON paycodes.paycode = postedpunches.paycode
AND paycodes.effectivedate IN (SELECT MAX(b.effectivedate)
FROM paycodes b
WHERE b.paycode = paycodes.paycode
AND b.effectivedate <= postedpunches.punchdated)
a column may not be outer-joined to a subquery
Contributor
2109 Points
2884 Posts
Re: Need to Rewrite the Query, Error: a column may not be outer-joined to a subquery
May 11, 2010 09:09 PM|thuhue|LINK
Please check here:
http://www.tek-tips.com/viewthread.cfm?qid=1462752&page=1
Contributor
2350 Points
1014 Posts
Re: Need to Rewrite the Query, Error: a column may not be outer-joined to a subquery
May 12, 2010 06:33 AM|duttavr|LINK
Thanks for providing the links, but I dont' think so the soution provided by mufasa works as still it uses e.emp_id in subquery.
My Collegue helped me in this regard. Following is the solutions with out using outerjoined table in subquery. My Big Thanks to Vinod.
SELECT count(*)
FROM postedpunches
left outer join (select * from paycodes
where paycodes.effectivedate IN
(SELECT MAX(b.effectivedate)
FROM paycodes b, postedpunches p
WHERE b.paycode = paycodes.paycode
AND b.effectivedate <= p.punchdate))paycodes
on paycodes.paycode = postedpunches.paycode
Contributor
2109 Points
2884 Posts
Re: Need to Rewrite the Query, Error: a column may not be outer-joined to a subquery
May 12, 2010 08:56 PM|thuhue|LINK
You said:"My Big Thanks to Vinod." Please clarify:
Who is Vinod?
What and where is the help you get from Vinod?
Contributor
2350 Points
1014 Posts
Re: Need to Rewrite the Query, Error: a column may not be outer-joined to a subquery
May 13, 2010 03:44 AM|duttavr|LINK
He is my colleague in my company.
Contributor
2109 Points
2884 Posts
Re: Need to Rewrite the Query, Error: a column may not be outer-joined to a subquery
May 15, 2010 01:24 AM|thuhue|LINK
In Hyderabad, India?