Re: Help with SQL Update using join files

The bin field is BABLOC and I am selecting only the value WHS.

On Sun, Aug 26, 2012 at 10:59 AM, Jack Callahan <jjcllhn@xxxxxxxxx> wrote:

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 )
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.