Re: Group by

On Fri, 13 May 2005 21:03:12 +0200, "Cor van Loon" <vanloon_at_planet.nl> wrote:

>Hi,
>
>I''m converting a sql-statement with a 'group by' to Oracle 8i .
>
>Select Tab1.f1, Tab1.fk,
> (select field from Tab2 where tab2.pk=Tab1.fk) as description,
> count(*)
>from Tab1
>group by Tab1.f1, Tab1.fk
>
>Oracle reports an error "ORA-00979 Not a GROUP BY expression"
>As soon as I remove the subquery the statements works fine (but is mis the
>value).
>Does Oracle 8i accepts subqueries as expression in the select-part?
>
>Kind regards,
>
>Cor
>

Oracle != Sqlserver and your subquery is not a subquery but an inline view

select tab1.f1, tab1.fk, field description, aantal from
(select tab1.f1, tab1.fk, count(*) aantal  from tab1
group by tab1.f1, tab1.fk
),
tab2
where tab2.pk = tab1.fk

And no, Oracle 8i (desupported) does not accept inline views in the select list. But that is no problem, as you see above.

--
Sybrand Bakker, Senior Oracle DBA