Re: Using Group By with Expressions

patrick_at_pcgi.com wrote:
>
> Hi.
>
> I'm using Oracle 7.2. I have a SQL Statement that looks roughly
>
> like this:
>
> SELECT TBL.COL1,
>
> TBL.COL2,
>
> DECODE ( TBL.COL3, "Value", TBL.COL3, TBL.COL4 ),
>
> SUM ( TBL.COL5 )
>
> FROM TBL
>
> GROUP BY COL1,
>
> COL2,
>
> ????
>
>
>
> Of course the ??? don't work. What I need to know is, how do I
>
> reference the computed column in my Group By? If I don't include
>
> it I get an error, and besides, it is legitimately part of the
>
> grouping. What I've tried:
>
> 1) Reference by position (Group By 1, 2, 3)
>
> 2) Asign an alias for the expression and group by that
>
> 3) Copy the expression into the group by
>
> None of these work. Any ideas? Not to fan any flames, but, gosh,
>
> I can do this in SQL Server ( ;-) ).
>
> Thanks!
>
> Patrick Wright
>
> Panttaja Consulting Group
>
> www.pcgi.com
>
> --
>
> Posted using Reference.COM http://www.reference.com
> Browse, Search and Post Usenet and Mailing list Archive and Catalog.
>
> InReference, Inc. accepts no responsibility for the content of this posting.

Your third approach (copying the expression into the GROUP BY) should work -- it always has for me. Just be careful not to copy any column alias along with the expression.

Barry S. Received on Thu May 22 1997 - 00:00:00 CDT