Re: How to "Group By" a calculated field?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 10 Jul 2001 06:45:38 -0700
Message-ID: <9if0u20319@drn.newsguy.com>

In article <994757278.634582_at_proxy.storm.co.za>, "Lee says...
>
>I am trying to construct a query that requires grouping by a calculated
>value (something like "to_char(:RptDate, 'MM')-to_char(OrderDate, 'MM')". I
>would have created a view that does the calculation, and used that, but the
>calculation is based on a parameter value. So how can I get around this?
>
>Thanks.
>
>Lee.
>
>

select ...., to_char(:RptDate, 'MM')-to_char(OrderDate, 'MM'), ...
  from ...
group by ..., to_char(:RptDate, 'MM')-to_char(OrderDate, 'MM')

select ..., C, ....
  from ( select ...., to_char(:RptDate, 'MM')-to_char(OrderDate, 'MM') C, ...
           from ...
       )

group by ...., C

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp

Received on Tue Jul 10 2001 - 08:45:38 CDT