Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2005

    Unanswered: Oracle 8.1.7 subselect

    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.

    UPDATE pac.item_fcst_rollup
       			   SET new_oh = old_oh + (SELECT SUM (old_oh)
                   			     	        FROM pac.item_fcst_rollup
                  						   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;

  2. #2
    Join Date
    Jan 2004
    Croatia, Europe
    Provided Answers: 5
    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.

  3. #3
    Join Date
    Jun 2004
    Liverpool, NY USA
    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 09:19.
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts