Re: Oracle SQL Query: Help needed, Please.
Date: Wed, 22 Apr 1998 16:26:11 +0530
Message-ID: <353DCCCB.FBFE6AB8_at_hotmail.com>
Hai
[Quoted] Adding to the one I sent yesterday you can avoid view creation by replcaing view vt1 like below
if you have to do it in a single query.,
COLUMN ENR FORMAT A10
select c.enr, c.c1, d.c2
from
(select b.enr enr, count(a.enr) c1
from tdat a,
(select to_char(enr,'mon-yy') enr from tdat
union
select to_char(ter,'mon-yy') from tdat) b
where b.enr = to_char(a.enr(+),'mon-yy')
group by b.enr) c,
(select b.enr enr, count(a.ter) c2
from tdat a,
(select to_char(enr,'mon-yy') enr from tdat
union
select to_char(ter,'mon-yy') from tdat) b
where b.enr = to_char(a.ter(+),'mon-yy')
group by b.enr) d
where c.enr = d.enr
order by substr(c.enr,5,6)
/
Regards
Suresh Kumar
T Suresh kumar wrote:
> Hai
> 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 Wed Apr 22 1998 - 12:56:11 CEST