Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2005
    Posts
    220

    Unanswered: Insert w/ subselect

    I am trying to run this insert query and I keep getting the error below. It appears to be a problem with the subselect

    Code:
    INSERT INTO pac.stock_lookup_tmp
      SELECT a.item,
               a.loc, 
          	   a.oh,       			  					 	   	 				  	 										 
                 0,        																									 
                 decode(a.loc, 'ATL', a.p_pdcstockingind,'N'),             											
                 decode(a.loc, 'LAN', a.p_pdcstockingind,'N'),            											
                 decode(a.loc, 'NEW', a.p_pdcstockingind,'N'),            											
                 decode(a.loc, 'ROC', a.p_pdcstockingind,'N'),            											
                 decode(a.loc, 'SEA', a.p_pdcstockingind,'N'),            											
                 decode(a.loc, 'TOR', a.p_pdcstockingind,'N'),            											
                decode(a.loc, 'OKC', a.p_pdcstockingind,'N')             													  
    	   (select max(decode(b.precedence,1,b.stocking_loc)) from pac.stock_lookup b where b.non_stocking_loc = a.loc),  
    	   (select max(decode(b.precedence,2,b.stocking_loc)) from pac.stock_lookup b where b.non_stocking_loc = a.loc),  
    	   (select max(decode(b.precedence,3,b.stocking_loc)) from pac.stock_lookup b where b.non_stocking_loc = a.loc),  
    	   (select max(decode(b.precedence,4,b.stocking_loc)) from pac.stock_lookup b where b.non_stocking_loc = a.loc),  
    	   (select max(decode(b.precedence,5,b.stocking_loc)) from pac.stock_lookup b where b.non_stocking_loc = a.loc)   
    	  FROM stsc.sku a
    	 WHERE a.item = sku_rec.item
     	   AND a.loc  = sku_rec.loc
       AND a.item IN (SELECT item FROM item	WHERE p_replenishmentcd	= 'Q' AND p_fcstoptionsw = 1 AND p_acdcode <> 'D')
    	   AND p_pdcstockingind IN ( 'S','M','N', 'R' )
       AND p_acdcode <> 'D'
       AND oh <> 0;
    ;

    ERROR at line 70:
    ORA-06550: line 70, column 10:
    PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
    ( ) - + mod not null others <an identifier>
    <a double-quoted delimited-identifier> <a bind variable>
    table avg count current exists max min prior sql stddev sum
    variance execute multiset the both leading trailing forall
    year month DAY_ HOUR_ MINUTE_ second TIMEZONE_HOUR_
    TIMEZONE_MINUTE_ time timestamp interval date
    <a string literal with character set specification>
    <a number> <a single-quoted SQL stri
    ORA-06550: line 70, column 117:
    PLS-00103: Encountered the symbol "," when expecting one of the following:
    ; return returning and or
    ORA-06550: line 75, column 9:
    PLS-00103: Encountered the symbol "FROM" when expecting one of the following:
    , ; for <an identifier>
    <a double-quoted delimited-identifier> group having intersect
    minus order start union where connect
    The symbol ", was inserted
    ORA-06550: line 87, column 4:
    PLS-00103: Encountered the symbol "UPDATE"

  2. #2
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    The first what seems be suspected to me is you have ; twice at the end of statement
    Last edited by madafaka; 10-18-05 at 18:13.

  3. #3
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    I see another one:
    there's comma (,) missing at line no 12

  4. #4
    Join Date
    Sep 2005
    Posts
    220
    I posted the wrong version...sorry

    Code:
    INSERT INTO pac.stock_lookup_tmp
    		    SELECT a.item,
    	               a.loc, 
                	   a.oh old_oh,     			  					 	   	 				  	 								  -- OnHand Qty
    			       0 new_oh,        																							  -- New OnHand Qty
                       decode(a.loc, 'ATL', a.p_pdcstockingind,'N'),             													  -- Does ATL stock the item ?
                       decode(a.loc, 'LAN', a.p_pdcstockingind,'N'),            													  -- Does LAN stock the item ?
                       decode(a.loc, 'NEW', a.p_pdcstockingind,'N'),            													  -- Does NEW stock the item ?
                       decode(a.loc, 'ROC', a.p_pdcstockingind,'N'),            													  -- Does ROC stock the item ?
                       decode(a.loc, 'SEA', a.p_pdcstockingind,'N'),            													  -- Does SEA stock the item ?
                       decode(a.loc, 'TOR', a.p_pdcstockingind,'N'),            													  -- Does TOR stock the item ?
    			       decode(a.loc, 'OKC', a.p_pdcstockingind,'N'),            													  -- Does OKC stock the item ?
    				   (select max(decode(b.precedence,1,b.stocking_loc)) from pac.stock_lookup b where b.non_stocking_loc = a.loc),  -- RollUp Precedence 1
    				   (select max(decode(b.precedence,2,b.stocking_loc)) from pac.stock_lookup b where b.non_stocking_loc = a.loc),  -- RollUp Precedence 2
    				   (select max(decode(b.precedence,3,b.stocking_loc)) from pac.stock_lookup b where b.non_stocking_loc = a.loc),  -- RollUp Precedence 3
    				   (select max(decode(b.precedence,4,b.stocking_loc)) from pac.stock_lookup b where b.non_stocking_loc = a.loc),  -- RollUp Precedence 4
    				   (select max(decode(b.precedence,5,b.stocking_loc)) from pac.stock_lookup b where b.non_stocking_loc = a.loc)   -- RollUp Precedence 5
    	   		  FROM stsc.sku a
    	   		 WHERE a.item = sku_rec.item
    	     	   AND a.loc  = sku_rec.loc
    			   AND a.item IN (SELECT item FROM item	WHERE p_replenishmentcd	= 'Q' AND p_fcstoptionsw = 1 AND p_acdcode <> 'D')
    	   		   AND p_pdcstockingind IN ( 'S','M','N','R' )
    			   AND p_acdcode <> 'D'
    			   AND oh <> 0;

  5. #5
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    what is sku_rec ? do you run it in some loop or something?

  6. #6
    Join Date
    Sep 2005
    Posts
    220
    yes, it does run in a loop

Posting Permissions

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