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

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 aggregate 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() function 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?
thank
-Craig
Planned_Qty = MAX(isnull((select sum(t_Table_b.Planned_Qty
FROM t_Mfg_BOM t_Table_
WHER
t_Table_a.My_Location_Code = t_Table_b.My_Location_Code AN
t_Table_a.Mfg_Order_Nbr = t_Table_b.Mfg_Order_Nbr AN
t_Table_a.Mfg_Order_Operation_Nbr = t_Table_b.Mfg_Order_Operation_Nbr AN
t_Table_a.Item_Nbr = t_Table_b.Item_Nbr AN
((t_Table_a.BOM_Nbr = t_Table_b.BOM_Nbr) o
(t_Table_a.BOM_Nbr = t_Table_b.Alt_BOM_Nbr) o
(t_Table_a.Alt_For_BOM_Nbr = t_Table_b.BOM_Nbr) O
(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
--sql