Re: help_sql_please

Reid Lai wrote:
>
> enquad_at_world.std.com (MWRA ENQUAD) wrote:
>
> >Could someone please help me with the following:
> >When I write a query with the following format I get the error (ORA-00979)
> >message "not a GROUP BY expression" at the first decode statement
> >in the select clause.
> >When I add the decode selections to my group by clause, Oracle
> >dosen't like this either (ORA-00998) "Must name this expression with a
> >column alias.
> >Is there a way out that will allow me to use decode statements rather
> >than multiple virtual table joins? (My actual query uses 24 decode statement
> >and the corresponding query with virtual tables is unwieldy)
 

> >Thanks in advance!
> >Doug Hersh -- enquad_at_world.std.com
 

> >select
> > T1.VALUE,
> > T2.VALUE,
> > Decode(arguments) alias_1,
> > Decode(arguments) alias_2
> >from
> > TABLE1 T1,
> > TABLE2 T2
> >where
> > conditions
> > and joins
> >group by
> > T1.VALUE,
> > T2.VALUE
> >;
>
> Try this :
>
> select
> T1.VALUE,
> T2.VALUE,
> SUM( Decode(arguments) ) alias_1,
> SUM( Decode(arguments) ) alias_2
> from
> TABLE1 T1,
> TABLE2 T2
> where
> conditions
> and joins
> group by
> T1.VALUE,
> T2.VALUE ;
One could take it to the next logical extent, include the decode in the group by:

 select

         T1.VALUE,
         T2.VALUE,
         SUM( Decode(arguments) ) alias_1,
         SUM( Decode(arguments) ) alias_2
 from
         TABLE1 T1,
         TABLE2 T2
 where
         conditions
         and joins
 group by
         T1.VALUE,
         T2.VALUE
         Decode(arguments),
         Decode(arguments);

I cannot recall if one needs/wants the alias in the group by.

Mike Krolewski. Received on Tue Jun 04 1996 - 00:00:00 CEST