Cannot perform an aggregate function on an expression containing an aggregate or a sub

I have a quick question. This SQL executes fine in Sybase, but returns the
following error in MS SQL Server:
"Cannot perform an aggregate function on an expression containing an aggrega
te or a subquery." The problem is clear to me. The aggregate function Max(
) is being performed on the results of another aggregate function sum(). If
I take out the MAX() funct
ion it works fine, but with incorrect results. The solution is not so clear
to me.
Do you have any thoughts about how to accomplish this using MS SQL Server?
thanks
-Craig-
Planned_Qty = MAX(isnull((select sum(t_Table_b.Planned_Qty)
FROM t_Mfg_BOM t_Table_b
WHERE
t_Table_a.My_Location_Code = t_Table_b.My_Location_Code AND
t_Table_a.Mfg_Order_Nbr = t_Table_b.Mfg_Order_Nbr AND
t_Table_a.Mfg_Order_Operation_Nbr = t_Table_b.Mfg_Order_Operation_Nbr AND
t_Table_a.Item_Nbr = t_Table_b.Item_Nbr AND
((t_Table_a.BOM_Nbr = t_Table_b.BOM_Nbr) or
(t_Table_a.BOM_Nbr = t_Table_b.Alt_BOM_Nbr) or
(t_Table_a.Alt_For_BOM_Nbr = t_Table_b.BOM_Nbr) OR
(t_Table_b.Alt_For_BOM_Nbr = t_Table_a.Alt_For_BOM_Nbr))),1))Put the SELECT with the subquery in a derived table and then GROUP it:
SELECT planned_qty = MAX(qty), ...
FROM
(SELECT ISNULL(
(SELECT SUM(t_Table_b.Planned_Qty)
FROM t_Mfg_BOM, t_Table_b
WHERE...)
,1)
FROM ...) AS X(qty, ...)
David Portas
SQL Server MVP
--