(Oracle)Getting multiple column when using correlated subquery

Question

My(Scott) goal is to get manager's name ,salary and deptno using correlated subquery. As below, I could get expected result, but similar subquery has been used several times. Is there a another neat way(without repeated similar pattern as below)?

SELECT
    O.ENAME EMP_NAME
    ,(SELECT DISTINCT FIRST_VALUE(I.ENAME) OVER (PARTITION BY NULL ORDER BY I.SAL DESC)
        FROM SCOTT.EMP I
        WHERE I.EMPNO=O.MGR  --correlated to outer
     ) AS MGR_NAME
     ,(SELECT DISTINCT FIRST_VALUE(I.SAL) OVER (PARTITION BY NULL ORDER BY I.SAL DESC)
        FROM SCOTT.EMP I
        WHERE I.EMPNO=O.MGR  --correlated to outer
     ) AS MGR_SAL
     ,(SELECT DISTINCT FIRST_VALUE(I.DEPTNO) OVER (PARTITION BY NULL ORDER BY I.SAL DESC)
        FROM SCOTT.EMP I
        WHERE I.EMPNO=O.MGR  --correlated to outer
     ) AS MGR_DEPTNO
    from SCOTT.EMP O;

Simple outer join to the emp table to get the managers record. Each employee has at most one manager, so performing FIRST_VALUE analytic function with a DISTINCT seems to be a bit of overkill. — Marmite Bomber
– Marmite Bomber, Commented Sep 2, 2020 at 15:10

Ranagal · Accepted Answer · 2020-09-02 15:13:06Z

2

select  e.ename emp_name, m.ename mgr_name, m.sal mgr_sal, m.deptno mgr_deptno
from    emp e left join emp m
on      e.mgr = m.empno
order   by m.deptno, e.ename;

Output:

answered Sep 2, 2020 at 15:13

Ranagal

3171 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

(Oracle)Getting multiple column when using correlated subquery

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related