×
The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.
Your subselect is expected to return a scalar value (which would be the
value of ifqcm for one one row), but the error is telling you multiple rows
are being returned.
(select ifqcm from vinitmb join vinbini
on bacmp = ifcomp and baloc = ifloc and baitem = ifitem
where ifqcm <> baqcm and babloc = 'WHS')
Given what you've stated, there can be multiple bins for each
company/location/item unless there is some rule you haven't mentioned that
enforces a single bin per item at each location.
Where file vinitmb is unique at the company/location/item level and vinbini
is unique at the company/location/item/bin level.
You could use grouping functions ( sum( ifqcm )- or max( ifqcm ) ) to
ensure a single value is returned.
UPDATE vinbini
SET baqcm = ( SELECT SUM( ifqcm ) FROM vinitmb JOIN vinbini ON bacmp=ifcomp
AND baloc=ifloc AND baitem=ifitem WHERE SUM( ifqcm)<>baqcm AND babloc='WHS'
GROUP BY bacmp,baloc,baitem )
WHERE EXISTS ( SELECT 1 FROM vinitmb JOIN vinbini ON bacmp=ifcomp AND
baloc=ifloc AND baitem = ifitem WHERE SUM( ifqcm )<>baqcm AND babloc='WHS'
GROUP BY bacmp,baloc, baitem )
midrange.com
