Clever Geek Handbook

SQL Server Migration Apply Query to Oracle

Which should be equivalent to below Sql server query in Oracle:

select dd.dname, e.ename
from emp e
outer apply 
(select top 1 dname from dept d where d.did=e.did order by bdate) dd

Note that the actual request is very different, but the concept is the same. Please forgive me for any syntax error in the above request.

I tried to execute Oracle query:

select dd.dname, e.ename
from emp e
left join 
(select * from 
(select dname from dept d where d.did=e.did order by bdate)
where rownum=1) dd

But this results in below error:

Error at Command Line:6 Column:18
Error report:
SQL Error: ORA-00905: missing keyword
00905. 00000 -  "missing keyword"
*Cause:    
*Action:
+3


source to share


2 answers


You can order the lines first and then take the first lines:

select e.ename, d.dname
  from emp e
  left join  (
      select dept.dname, dept.did, row_number()
         over (partition by did order by bdate) rn from dept) d
    on e.did = d.did and d.rn=1


SQLFiddle

0


source


Inclusion of a sentence after merging. So, I convert the request to a group on demand and it works:



select dd.dname, e.ename
from emp e
left join 
(select min(bdate), dname, did from dept d group by dname, did)dd
on dd.did=e.did
+1


source






More articles:


All Articles