More than 1 value returned

[kacie]

I have a stored procedure that is used in a Crystal Report. When I run the query I get &quot;'Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.&quot; The code is:

UPDATE #tbl_company
SET desig_creamt = (SELECT sum(CH.creamt)
FROM #tbl_company TC INNER JOIN
frcrehis CH ON (TC.frid = CH.cmpid AND TC.capcod = CH.capcod) INNER JOIN
frdonmst DM1 ON CH.frid = DM1.frid
WHERE DM1.doncls IN ('I', 'ST')
GROUP BY CH.cmpid)

Please help I have spinning my wheels for two days on this. Thanks

 

[SQLBill]

Run just this part:

SELECT sum(CH.creamt)
FROM #tbl_company TC INNER JOIN
frcrehis CH ON (TC.frid = CH.cmpid AND TC.capcod = CH.capcod) INNER JOIN
frdonmst DM1 ON CH.frid = DM1.frid
WHERE DM1.doncls IN ('I', 'ST')
GROUP BY CH.cmpid

How many returns do you get? You have to narrow it down so it will only return one value as you can only put one value into a variable at a time.

-SQLBill

 

[kacie]

I ran just that section of the SP. I included the cmpid on each row. There are only 21 rows with no dups. There are also no dups in the tbl_company table. Can I do an Update with an aggregate total with a group by the way I have it? This is the only thing I can think of.

 

[JamesLean]

Are you trying to update each row in #tbl_company with the total for that specific company, based on the cmpid? If so, try using a derived table:

UPDATE #tbl_company
SET desig_creamt = dt.tot
FROM #tbl_company c JOIN
  (
  SELECT CH.cmpid, SUM(CH.creamt) AS tot
  FROM #tbl_company TC
    INNER JOIN frcrehis CH ON (TC.frid = CH.cmpid AND TC.capcod = CH.capcod)
    INNER JOIN frdonmst DM1 ON CH.frid = DM1.frid
  WHERE DM1.doncls IN ('I', 'ST')
  GROUP BY CH.cmpid
  } dt ON c.cmpid = dt.cmpid

--James