corelated query with left join is not working with rownum=1
947771Mar 1 2013 — edited Mar 2 2013Hi,
select e.id, e.name ,s.salaray as latest
from emp e
left join dep d on d.empid=e.empid
left join sal s on e.empid =( select empid from
(select empid from sal where empid=e.empid
order by salaryhistoryno desc ) where rownum=1
)
it is showing error
Error at Command Line:1 Column:0
Error report:
SQL Error: ORA-01799: a column may not be outer-joined to a subquery
01799. 00000 - "a column may not be outer-joined to a subquery"
*Cause: <expression>(+) <relop> (<subquery>) is not allowed.
*Action: Either remove the (+) or make a view out of the subquery.
In V6 and before, the (+) was just ignored in this case.
S1) so i dicided to use group by and took some how took latest salary then also it requirs "JOIN" instead of "LEFT JOIN" after using "join" AND GROUP BY
it started working , but i do not want this.
s2) Then i tried to use correlation in select clause .
it is working some how but there are may columns of sal table i want to use in select list so that will make my query slow.
yours sincerely
Edited by: 944768 on Mar 1, 2013 12:36 AM