Re: group by / columns

In our last gripping episode "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
>
>

Not to my knowledge.

The documentation lists expressions as valid in a GROUP BY but does not list positional notation or column aliases. When using an alias Oracle will report "ORA_00904: invalid column name" and when using positional notation Oracle will report "ORA-00979: not a GROUP BY expression". It would appear you are stuck using the actual expression from the select statement.

--
David Fitzjarrell
Oracle Certified DBA


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