Re: Oracle SQL Query: Help needed, Please.

From: T Suresh kumar <sureshkt_at_hotmail.com>
Date: Tue, 21 Apr 1998 12:28:35 +0530
Message-ID: <353C439A.73C6E055_at_hotmail.com>

Hai

[Quoted]     Below is the query I could write., I had to write 2 times the same query in from clause

    because Outer join is not accepted in OR conditions.,

    COLUMN enr FORMAT A10
    create view vt1 as (select to_char(enr , 'mon-yy') enr from t1

                                  union
                                  select    to_char(ter,'mon-yy') from

t1);

    select c.enr,c.c1, d.c2
    from

         (select   vt1.enr enr, count(a.enr) c1
          from    t1 a, vt1
          where    vt1.enr = to_char(a.enr(+), 'mon-yy')
          group by vt1.enr)  c ,
          (select    vt1.enr enr, count(a.ter) c2
          from    t1 a, vt1
         where    vt1.enr = to_char(a.ter(+), 'mon-yy')
         group by vt1.enr)  d

   where c.enr = d.enr
   order by substr(c.enr,5,6)

I hope this will help you in sorting out the problem.

Regards
Suresh

edawad_at_yahoo.com wrote:

> -- I would appriciate your help on this Query:
> --
> -- The following table is created:
> --
> create table t1 (
> emp_id number(2)
> ,enr date
> ,ter date
> );
> --
> -- Then the following rows were inserted into that table:
> --
> insert into t1 values (1, to_date('02-jan-97'), to_date('02-jan-98'));

>

> insert into t1 values (2, to_date('10-jan-97'), to_date('30-mar-97'));

>

> insert into t1 values (3, to_date('11-apr-97'), to_date('13-nov-97'));

>

> insert into t1 values (4, to_date('12-may-97'), to_date('13-nov-97'));

>

> insert into t1 values (5, to_date('20-nov-97'), to_date('14-jan-98'));

>

> insert into t1 values (6, to_date('30-jun-97'), to_date('15-feb-98'));

>

> commit;
> /*
> I want the following result from a single query:

>

> month_year enrollments terminations
> ---------- ----------- -------------
> jan-97 2 0
> mar-97 0 1
> apr-97 1 0
> may-97 1 0
> jun-97 1 0
> nov-97 1 2
> jan-98 0 2
> feb-98 0 1

>

> Thank you in advance.
> */

>

> -----== Posted via Deja News, The Leader in Internet Discussion
> ==-----
> http://www.dejanews.com/ Now offering spam-free web-based
> newsreading
Received on Tue Apr 21 1998 - 08:58:35 CEST