Re: Help constructing SQL

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 19 Jan 2000 10:36:06 -0500
Message-ID: <tqlb8soupvo9j7qfejl0t2v98q79b16afq@4ax.com>

A copy of this was sent to Greg Akins
<gakinsNOgaSPAM_at_gatewayhealthplan.com.invalid> (if that email address didn't require changing) On Wed, 19 Jan 2000 06:40:44 -0800, you wrote:

>Hi,
>
>I have a table:
>
>Emp
>=========
>EmpNo
>DeptNo
>StartDate
>TermDate
>PositiveReview
>

ops$tkyte_at_8i> create table t ( empno int, deptno int, startdate date, termdate date, positiveReview int );

Table created.

ops$tkyte_at_8i> insert into t values ( 1, 10, sysdate, null, null );
ops$tkyte_at_8i> insert into t values ( 2, 10, sysdate, null, 1 );
ops$tkyte_at_8i> insert into t values ( 3, 10, sysdate, sysdate, null );
ops$tkyte_at_8i> insert into t values ( 4, 10, sysdate, sysdate, 1 );
ops$tkyte_at_8i> insert into t select empno, deptno+10, startdate, termdate,

positiveReview from T;

ops$tkyte_at_8i> commit;
Commit complete.

that gives me 2 current emps/dept.
2 terminated/dept.
one current with a postiveReview (and 1 current without a positive review and 2 terminated -- 1 with and 1 without a postive review).

ops$tkyte_at_8i> select deptno,

  2         sum(decode( termdate, NULL, 1, 0 )) Crnt_employed,
  3         sum(decode( termdate, NULL, 0, 1 )) Terminated,
  4         sum(decode( termdate, NULL, decode(positiveReview,NULL,0,1), 0 )) 
                      Had_pos_review

  5 from T
  6 where deptno is not null
  7 group by deptno
  8 /

    DEPTNO CRNT_EMPLOYED TERMINATED HAD_POS_REVIEW ---------- ------------- ---------- --------------

        10             2          2              1
        20             2          2              1

2 rows selected.

the sum(decode( termdate, NULL, 1, 0 )) says "if the row has a NULL termdate then contribute 1 to the answer, else contribute 0". that lets us compute the number of current employees (with a NULL termdate).

the sum(decode(termdate, null, 0, 0 )) does the opposite. constribute a 1 if the termdate is set else 0. sum that up by dept.

the sum(decode( termdate, NULL, decode(positiveReview,NULL,0,1), 0 )) is like:

   if ( termdate is NULL ) -- a current employee    then

      if ( positiveReview is NULL ) -- they do not have a positive review
      then
          return 0;
      else                          -- they do have a positive review
          return 1;
      end if;
  else                    -- not a current emp, always return 0 regardless...
      return 0;

  end if;      

>I need a report which looks like
>
>DeptNo Crnt_Employed Terminated(y/n) Had_Pos_Review
>=======================================================
>1 3 0 1
>2 4 1 0
>3 0 2 0
>
>So I tried:
>
>select B.DeptNo, b.Employed, a.Terminated
>from (select DeptNo, count(*) as Terminated
>from emp
>where DeptNo is not null
>and TermDate is not null
>group by DeptNo) a,
>(select DeptNo, count(*) as Employed
>from emp
>where DeptNo is not null
>and TermDate is null
>group by DeptNo) b
>where a.DeptNo(+) = b.DeptNo
>order by DeptNo
>
>The problem is: Since null values can be
>returned by the subqueries, I can't rely
>on any specific outerjoin to return the
>correct results.
>
>Can somebody give me suggestions for
>constructing the query. If it's obvious,
>please excuse my ignorance.
>
>-greg
>
>
>* Sent from RemarQ http://www.remarq.com The Internet's Discussion Network *
>The fastest and easiest way to search and participate in Usenet - Free!

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Jan 19 2000 - 09:36:06 CST