subquery error with group by

[timc]

On sqlserver 2000 SP 4 or SP3 boxes I have the below query. It will fail with Server: Msg 8624, Level 16, State 3, Line 6. If I comment out the group by in the subquery it works. have researched and there seems to be an issue wih the group by in subquery. ANyone see or resolved this?


declare @SiteID varchar(35),@BeginDate datetime, @EndDate datetime
set @SiteID = N'eastern'
set @BeginDate = N'01/01/2006'
set @EndDate = N'12/31/2006'
select v1.*, v2.*,
dbo.fnGetMaxTranscribe(v1.orderid)
from
mar_vw_rpt_orders_history v1
inner join
(Select orderid,hh,scheduletimesorderby
from mar_vw_history_mo
where mar_vw_history_mo.fullmedadmindate
between convert(datetime,(convert(varchar(10),@begindate,1 01)))
and convert(datetime,(convert(varchar(10),@enddate,101 )))
--Group By orderid,scheduletimesorderby,hh
) v2
on v1.orderid = v2.orderid
and v1.PatientCurrentSiteID = @siteid
order by v1.inmateid,v2.orderid,v2.scheduletimesorderby

[skhanal]

Why do you need to group by all three columns in the select list?. I don't see any aggregate in the query.

[timc]

I have found the problem, the group by is not the issue. Now I have another bug. We have a View which as a sub-query in it which works fine by itself. If however you have any soted procedure which uses a sub-query join with this view you get an internal sql error. Anyone seen or reported this?