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

    Unanswered: Help w/ Insert Statement

    I am getting this error:

    ORA-00979: not a GROUP BY expression

    When I try to run this query. Can I group by the subselected columns?

    Code:
    INSERT INTO pac.stock_lookup_tmp
         SELECT a.item,
    	        a.loc, 
    			a.oh,
    			0,
                max(decode(a.loc, 'ATL', a.p_pdcstockingind)) atl,
                max(decode(a.loc, 'LAN', a.p_pdcstockingind)) lan,
                max(decode(a.loc, 'NEW', a.p_pdcstockingind)) new,
                max(decode(a.loc, 'ROC', a.p_pdcstockingind)) roc,
                max(decode(a.loc, 'SEA', a.p_pdcstockingind)) sea,
                max(decode(a.loc, 'TOR', a.p_pdcstockingind)) tor,
    			max(decode(a.loc, 'OKC', a.p_pdcstockingind)) okc,
    			(select b.stocking_loc from pac.stock_lookup b where b.precedence = 1 and b.non_stocking_loc = a.loc),
    			(select b.stocking_loc from pac.stock_lookup b where b.precedence = 2 and b.non_stocking_loc = a.loc),
    			(select b.stocking_loc from pac.stock_lookup b where b.precedence = 3 and b.non_stocking_loc = a.loc),
    			(select b.stocking_loc from pac.stock_lookup b where b.precedence = 4 and b.non_stocking_loc = a.loc),
    			(select b.stocking_loc from pac.stock_lookup b where b.precedence = 5 and b.non_stocking_loc = a.loc)
    	   FROM stsc.temp_q_sku1 a
          GROUP BY a.item,a.loc,a.oh;

  2. #2
    Join Date
    Oct 2002
    Location
    greenwich.ct.us
    Posts
    279
    I suspect where you're having an error is at the "(select b.stocking_loc from pac.stock_lookup b where b.precedence = 1 and b.non_stocking_loc = a.loc)" queries.

    I would move those queries to the FROM clause and use them as an inline view.

  3. #3
    Join Date
    Sep 2005
    Posts
    220
    I'm not sure I understand what you mean.

  4. #4
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    Join pac.stock_lookup
    then decode using a max(decode(b.precidence,1,b.stocking_loc)) as Loc1

    something like that.

    syntax of decode may not be exact as I usually use case statement.

    I am assuming that you want 1 column per location
    your output set would have 16 columns

Posting Permissions

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