Re: Selects within selects - problem with group bys.

"Matthew Barrett" <matt_barrett_at_tesco.net> a écrit dans le 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.
>

If there is only one value V for each A, you can write: select a,b,max(c),v
from tblx, tbly
where t=a
group by a,b,v;

If there is zero or one value, you have to outer join: select a,b,max(c),v
from tblx, tbly
where t(+)=a
group by a,b,v;

If there may be more than one value:
select a,b,m,v
from tblx, (select a,b,max(c) m from tbly group by a,b) where t = a;

--
Have a nice day
Michel