How to solve this error – ORA-22818: subquery expressions not allowed?

SELECT first_name, (select min(salary) from hr.employees where department_id = 103), (select max(salary) from hr.employees where department_id = 103)
from hr.employees where hr.employees.department_id = 103;

But it shows an error:

subquery expressions not allowed here

Do you know how to solve the error?

Answer

LEFT JOIN a derived table (aka inline view) that returns each department’s min and max salary:

SELECT e.first_name,
       e2.min_sal,
       e2.max_sal
from hr.employees e
left join (select department_id, min(salary) min_sal, max(salary) max_sal
           from hr.employees
           group by department_id) e2
  on e.department_id = e2.department_id
where e.department_id = 103;