On 22 mayo, 03:01, DA Morgan <damor..._at_psoug.org> wrote:
> valigulawrote:
> > I need to calculate two differetns totals from the following script.
> > one the filter per year and another that return everything. At the
> > moment i´m looking at the roolup function but .....
> > is it better if i do another subquery ...???
>
> > thanks
>
> > /* Formatted on 2007/05/21 09:08 (Formatter Plus v4.8.7) */
> > SELECT DISTINCT tip.tip_desc, tip.tip_clasact, ma.anyobaja
> > -- , (select count (*) from ma_con_matriculas where mat_codigo
> > is not null ) totalaltas
> > -- ,(select count (*) from ma_con_matriculas where to_char
> > (mat_fecha_alta , 'YYYY')= '2006' ) totalanyo
> > , bajas.r, bajas.d, bajas.p
> > FROM tm_con_clases_medios me,
> > (SELECT ma.cla_codigo, ma.mat_codigo,
> > TO_CHAR (ma.mat_fecha_baja, 'YYYY') AS
> > anyobaja
> > FROM tm_con_clases_medios me, ma_con_matriculas ma
> > WHERE me.cla_codigo = ma.cla_codigo
> > AND ma.cla_codigo = me.cla_codigo
> > AND ma.mat_fecha_baja IS NOT NULL) ma,
> > (SELECT tip_codigo, tip_desc,
> > CASE
> > WHEN tip_clasact IN
> > ('82', '82', '87', '88')
> > THEN tip_clasact || ' +500'
> > WHEN tip_clasact IN
> > ('77', '78', '79','80', '81','86'
> > )
> > THEN tip_clasact || ' -500'
> > WHEN tip_clasact IN ('70', '85')
> > THEN tip_clasact || ' kioscos'
> > END tip_clasact
> > FROM tm_con_tipos_medios) tip,
> > (SELECT mat_codigo, TO_CHAR (mat_fecha_baja,
> > 'YYYY') a, mov_tipmov,
> > COUNT (DECODE (mov_tipmov, '10',
> > mov_tipmov)) r,
> > COUNT (DECODE (mov_tipmov, '11',
> > mov_tipmov)) d,
> > COUNT (DECODE (mov_tipmov, '12',
> > mov_tipmov)) p
> > FROM ma_con_matriculas LEFT OUTER JOIN
> > hi_con_movimientos
> > ON mat_codigo = mov_matricula
> > GROUP BY mat_codigo,
> > TO_CHAR (mat_fecha_baja, 'YYYY'),
> > mov_tipmov) bajas
> > WHERE me.cla_codigo = ma.cla_codigo
> > --AND tip.tip_codigo = me.tip_codigo
> > AND ma.cla_codigo = me.cla_codigo
> > AND bajas.mat_codigo = ma.mat_codigo
> > and anyobaja = '2006'
> > GROUP BY tip.tip_desc, tip.tip_clasact, bajas.r, bajas.d,
> > bajas.p , ma.anyobaja;
>
> Usually.
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org
.... Anyway ... its done ...
Received on Tue May 22 2007 - 03:15:28 CDT
