How to make Count return 0?

Ok here's the problem,
I have an employee table that, among other columns, contains these 3 columns:
emp_no, dept_no, job_no
dept_no is foreign key to the departments table and job_no is a foreign key to the jobs table
Now, i want to build a report that returns the number of employees in each department for each job.
So, say i have 2 departments and 3 jobs and in the employee table they are as such:
emp_no dept_no job_no
1 A 1
2 B 1
3 A 2
4 A 1
The output i want should look like this, in a matrix type report:
dept / job: 1 2 3
A 1 2 0
B 1 0 0
Now, unfortunetly, the matrix report ive built does not return a value where I would like it to return a 0, i get:
dept / job: 1 2
A 1 2
B 1
This is my select:
select count(e.emp_no), d.dept_no, j.job_no
from employee e, department d, job j
where e.job_no = j.job_no(+) and
d.dept_no = d.dept_no(+)
group by d.dept_no, j.job_no
Now, i am pretty sure that the solution is to incorporate an inline view to my select statement, but i would like help on how to build such a select statement.
Thanx in advance
Peace
Sakitah

Don't you just need to NVL the job_no column like:

select count(e.emp_no), d.dept_no, NVL(j.job_no,0) AS job_no
from employee e, department d, job j
where e.job_no = j.job_no(+) and
d.dept_no = d.dept_no(+)
group by d.dept_no, j.job_no

Well, i dont have Oracle on my machine right now to try it out. But i dont think that will work b/c to use nvl is to assume that a record is returned.
what i want is if no emp is in Job 3, then return a record with:
count - dept - job
0 - A - 3
but, no record is returned with job 3

This may help you. The inline view makes cartesian product job*department thus can be slow for large tables.

Code:

---

select count(e.emp_no), v.dept_no, v.job_no
from employee 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

---

Ales

Ales, thanx...ur code helped me get the results i wanted.
However, i then added in the WHERE clause:
and e.emp_type in (1,2)
This little statement undid what was achieved with the in-line view. It's back to square 1 now.
Why would adding that statement cause this problem and how to overcome it?

Thanx in advance
Peace,
Sakitah

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;