Must outer-join on all fields in EMP
You are outer-joining the EMP table, so all references in WHERE clauses to EMP fields must specify the outer-join operator.
Instead of:
" and e.emp_type in (1,2) "
what you want is:
" and e.emp_type(+) in (1,2) "
But Oracle does not allow this! Bummer.
"ORA-01719: outer join operator (+) not allowed in operand of OR or IN"
As long as we have a subquery, we might as well have two:
select count(e.emp_no), v.dept_no, v.job_no
from
(
select emp_no,dept_no,job_no
from employee
where emp_type in (1,2)
) e,
(
select d.dept_no, j.job_no
from job j, department d
) v
where e.job_no (+) = v.job_no
and e.dept_no (+) = v.dept_no
group by v.job_no, v.dept_no
order by 3,2;