DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: How to make Count return 0?

  1. #1
    Join Date
    Oct 2001
    Posts
    22
    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

  2. #2
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    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
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: oracle-base.com
    My blog: oracle-base.com/blog

  3. #3
    Join Date
    Oct 2001
    Posts
    22

    Why NVL

    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

  4. #4
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    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

  5. #5
    Join Date
    Oct 2001
    Posts
    22
    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

  6. #6
    Join Date
    Apr 2002
    Posts
    11

    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;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width