Does anyone know if there is a limitation in 8.1.7 with regards to insert/update statements with subselect?
I'm trying to run this query from PL/SQL and I keep getting a PLS-00103 error.
SET new_oh = old_oh + (SELECT SUM (old_oh)
WHERE atl_stocking_ind != 'S'
AND lan_stocking_ind != 'S'
AND new_stocking_ind != 'S'
AND roc_stocking_ind != 'S'
AND sea_stocking_ind != 'S'
AND tor_stocking_ind != 'S'
AND okc_stocking_ind != 'S')
WHERE atl_stocking_ind = 'S'
OR lan_stocking_ind = 'S'
OR new_stocking_ind = 'S'
OR roc_stocking_ind = 'S'
OR sea_stocking_ind = 'S'
OR tor_stocking_ind = 'S'
OR okc_stocking_ind = 'S'
AND item = sku_rec.item
AND non_stocking_loc = sku_rec.loc;
PLS-00103 does not refer directly to subselect limitations; it is a message from the parser which found a token (language element - SELECT in your query) that is inappropriate in this context.
I don't know whether you solved the problem or not (regarding the thread date), but this are two options I can suggest: first one is putting "old_oh" into the subquery (if it suits your needs), such as
SET new_oh = (SELECT old_oh + SUM (old_oh) FROM ...
This might, however, produce TOO-MANY-ROWS error as you'll need a "GROUP BY old_oh".
Second way would be running this UPDATE in SQL*Plus instead of as a part of PL/SQL block. Doing so, you'll avoid PLS-00103 error. Why? That's a good question, but I don't know how to explain it. Tried to figure it out - without success. The only thing that crosses my mind is that, somehow, PL/SQL engine works differently than SQL engine in such cases.
Shouldn't you have AND (item = sku_rec.item AND non_stocking_loc = sku_rec.loc) inside your sub-select to only add those items that are in the same item? Try something like
UPDATE pac.item_fcst_rollup a
SET a.new_oh = (SELECT a.old_oh + SUM(b.old_oh)
FROM pac.item_fcst_rollup b
WHERE b.atl_stocking_ind != 'S'
AND b.lan_stocking_ind != 'S'
AND b.new_stocking_ind != 'S'
AND b.roc_stocking_ind != 'S'
AND b.sea_stocking_ind != 'S'
AND b.tor_stocking_ind != 'S'
AND b.okc_stocking_ind != 'S'
AND b.item = sku_rec.item
AND b.non_stocking_loc = sku_rec.loc)
WHERE a.atl_stocking_ind = 'S'
OR a.lan_stocking_ind = 'S'
OR a.new_stocking_ind = 'S'
OR a.roc_stocking_ind = 'S'
OR a.sea_stocking_ind = 'S'
OR a.tor_stocking_ind = 'S'
OR a.okc_stocking_ind = 'S'
AND a.item = sku_rec.item
AND a.non_stocking_loc = sku_rec.loc;
Last edited by beilstwh; 11-08-05 at 10:19.
You do not need a parachute to skydive. You only need a parachute to skydive twice.