Re: Field Alias in SQL

Kuncoro Tri Atmoko wrote:

> First, I think you should create a view like this:
>
> create view VIEWA as
> select rtrim(substr(upper(email),instr(email,'@',1)), ' ') FIELD1
> from TABLEA;
>
> then you can query the data like :
>
> select FIELD1, COUNT(FIELD1)
> from VIEWA
> group by FIELD1
> order by 1 desc
>
> Thank's
>
> Kuncoro
> Jakarta, Indonesia

Or, if it's a one-time-problem the query can be stated like this:

 SELECT emailhost, COUNT (emailhost)
  FROM ( SELECT RTRIM (SUBSTR (UPPER (email), INSTR (email, '@', 1)), ' ') emailhost

           FROM tablea) tablea
 GROUP BY emailhost
 ORDER BY 1 desc

Does anybody have a good explanation why a columnalias couldn't be used in the where/group/order-parts of the statement?

ole c Received on Tue Aug 31 1999 - 02:14:41 CDT