Re: group by / columns

In article <TLST600291AC5_at_telestars.nl>,   "Sammy" <Sammy_at_somewhere.nl> wrote:
> Hi,
>
> I am migrating all SQL statements from SQLBase to Oracle (8.0.5.)
> In SQLBase it was possible to use the column numbers in group by and
 order
> by statements.
> Example
>
> Select @datevalue(...) As TheDate,
> @left(...) As Tframe,
> count(*) As Amount,
> (...)
>
> From (...)
>
> where (...)
>
> group by 2,1
> order by 2,1;
>
> This doesn't work with Oracle. I can use the assigned column names
> in the order by clause, but it seems that I have to use the full
 expression
> in the group by clause. In Oracle it would look like this:
>
> group by substr(...),to_date(...)
> order by Tframe,TheDate;
>
> Is there any other way I can refer to to expressions in the group by
 clause
> ?
>
> TIA,
>
> Sammy
>
>

The simple answer is NO.

The order by can use the position of the column ( 1, 2,3,...). In fact when writing a union, the order by clause must use positions.

Group by must be complete column information without the alias.

Most editors allow cut and paste. Write out the select, then build the group by clause.

--
Michael Krolewski
Rosetta Inpharmatics
mkrolewski_at_rii.com
              Usual disclaimers


Sent via Deja.com
http://www.deja.com/