Re: Selects within selects - problem with group bys.

"Matthew Barrett" <matt_barrett_at_tesco.net> wrote in message news:<hga57.45815$WS4.7062781_at_news6-win.server.ntlworld.com>...
> Hi all,
>
>
> I am having a problem converting some of my views from syabase to oracle 8i
> standard:
>
> They are basically a select within a select, e.g.
>
>
> CREATE OR REPLACE v_MyView (fielda,fieldb,fieldc,fieldd) AS
>
> SELECT A,B,MAX(c),(SELECT V FROM TBLX WHERE T = A)
>
> FROM TBLY GROUP BY A,B;
>
>
>
> I get an error basically saying that the (select v from tblx where t= a)
> needs to be in the group by,
>
> however, there is no way I can get it into the group by, also I don't want
> to group by fieldd.
>
>
> Any help would be much appreciated, as this will solve halve of the views I
> am having problems with.
>
>
> Thanks.
>
>
> Matthew.

Rephrase like this

select a, b, maxc
from
(select a, b, max(c) maxc
 from tbly
 group by a, b
 )
,
(select t, v
 from tblx)
where t = a

Hth,

Sybrand Bakker, Senior Oracle DBA Received on Wed Jul 18 2001 - 03:22:05 CDT