Replacing huge IF statement with SQL code
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Replacing huge IF statement with SQL code

  1. #1
    Join Date
    Aug 2003
    Posts
    100

    Replacing huge IF statement with SQL code

    This is driving me nuts and I need some help. I have a very large IF statement (see below. This is only a small section. It is actually 7X larger) that I am trying to convert to using a table. I've created a table (2nd box below) to hold the rollup information. This table says "If an item is nonstocking at ATL, check ROC to see if it's stocking. If not, check LAN, and so on.) Does anyone know if this is possible?

    Code:
    		
    IF sku_rec.loc = 'ATL' THEN
    
      IF 	roc_stocking IN ( 'S', 'M' ) THEN
      
      	INSERT INTO
      			stsc.temp_q_sku2
      	SELECT
      			*
      	FROM
      			stsc.sku
      	WHERE
      			item 	= sku_rec.item
      	AND
      			loc 	= 'ROC' ;
      
      ELSIF	lan_stocking IN ( 'S', 'M' ) THEN
      
      	INSERT INTO
      			stsc.temp_q_sku2
      	SELECT
      			*
      	FROM
      			stsc.sku
      	WHERE
      			item 	= sku_rec.item
      	AND
      			loc 	= 'LAN' ;
      
      ELSIF	new_stocking IN ( 'S', 'M' ) THEN
      
      	INSERT INTO
      			stsc.temp_q_sku2
      	SELECT
      			*
      	FROM
      			stsc.sku
      	WHERE
      			item 	= sku_rec.item
      	AND
      			loc 	= 'NEW' ;
      
      ELSIF	sea_stocking IN ( 'S', 'M' ) THEN
      
      	INSERT INTO
      			stsc.temp_q_sku2
      	SELECT
      			*
      	FROM
      			stsc.sku
      	WHERE
      			item 	= sku_rec.item
      	AND
      			loc 	= 'SEA' ;
      
      ELSE
      	DELETE FROM
      			stsc.temp_q_sku1
      	WHERE CURRENT OF
      			sku_cur;
      
    END IF;

    Code:
    NonStocking_Loc  Stocking_Loc   Precedence
    ATL                  ROC             1
    ATL                  LAN             2
    ATL                  NEW             3
    ATL                  SEA             4
    LAN                  ROC             1
    LAN                  ATL             2
    LAN                  NEW             3
    LAN                  SEA             4
    NEW                  SEA             1
    NEW                  ROC             2
    NEW                  ATL             3
    NEW                  LAN             4
    ROC                  ATL             1
    ROC                  NEW             2
    ROC                  LAN             3
    ROC                  SEA             4
    SEA                  NEW             1
    SEA                  ROC             2
    SEA                  ATL             3
    SEA                  LAN             4
    TOR                  ROC             1
    TOR                  LAN             2
    TOR                  ATL             3
    TOR                  SEA             4
    TOR                  NEW             5
    FYI...the atl_stocking is determined as such:
    Code:
    		
    BEGIN
       SELECT p_pdcstockingind
          INTO atl_stocking
         FROM stsc.sku
       WHERE item = sku_rec.item
           AND loc = 'ATL'
           AND p_acdcode	<> 'D' ;
    
       EXCEPTION WHEN NO_DATA_FOUND THEN
    	atl_stocking	:= 'N' ;
    
    END;

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Where do the values of roc_stocking, lan_stocking etc come from?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Aug 2003
    Posts
    100
    This is in a PL/SQL loop:
    Code:
                SELECT item, 
    			       loc,
    		           max(decode(loc, 'ATL', p_pdcstockingind,'N')) atl,
                       max(decode(loc, 'LAN', p_pdcstockingind,'N')) lan,
                       max(decode(loc, 'NEW', p_pdcstockingind,'N')) new,
                       max(decode(loc, 'ROC', p_pdcstockingind,'N')) roc,
                       max(decode(loc, 'SEA', p_pdcstockingind,'N')) sea,
                       max(decode(loc, 'TOR', p_pdcstockingind,'N')) tor,
    				   max(decode(loc, 'OKC', p_pdcstockingind,'N')) okc
                  INTO item,
    			       loc,
    			       atl_stocking,
    			       lan_stocking,
    				   new_stocking,
    				   roc_stocking,
    				   sea_stocking,
    				   tor_stocking,
    				   okc_stocking                                 
    			  FROM stsc.sku
    			 WHERE item = sku_rec.item
    			   AND loc = sku_rec.loc
    			   AND p_acdcode != 'D'
    			 GROUP BY item, loc;

  4. #4
    Join Date
    May 2005
    Posts
    10
    If the IF-ELSE loop is killing you,try to put the values of roc_stocking, lan_stocking in a GTT and then
    1. If you are in 10g use merge to insert/delete stsc.temp_q_sku2.
    2. If not in 10g loop thru GTT and insert using a decode in the where clause in the select statement.
    However IMHO I see this adding to overhead.

  5. #5
    Join Date
    Feb 2005
    Posts
    158
    Something like this should work (though I haven't got tables etc to do any testing on).


    declare
    type tab_precedence is table of varchar2(3) index by binary_integer;
    t_prec tab_precedence;
    type tab_nonstock is table of tab_precedence index by varchar2(3);
    t_nonstock tab_nonstock;
    t_val varchar2(3);
    function test (p_test in tab_precedence, p_roc in varchar2, p_lan in varchar2,
    p_new in varchar2, p_sea in varchar2, p_atl in varchar2) return varchar is
    v_val varchar2(3);
    begin
    for i in 1..5 loop
    if p_test(i) = 'ROC' and roc_stocking IN ( 'S', 'M' ) then return 'ROC';
    elsif p_test(i) = 'LAN' and roc_stocking IN ( 'S', 'M' ) then return 'LAN';
    elsif p_test(i) = 'NEW' and roc_stocking IN ( 'S', 'M' ) then return 'NEW';
    elsif p_test(i) = 'SEA' and roc_stocking IN ( 'S', 'M' ) then return 'SEA';
    elsif p_test(i) = 'ATL' and roc_stocking IN ( 'S', 'M' ) then return 'ATL';
    end if;
    end loop;
    return null;
    end;
    begin
    -- Initialise
    t_prec(1) := 'ROC';
    t_prec(2) := 'LAN';
    t_prec(3) := 'NEW';
    t_prec(4) := 'SEA';
    t_prec(5) := null;
    t_nonstock('ATL') := t_prec;
    t_prec(1) := 'ROC';
    t_prec(2) := 'ATL';
    t_prec(3) := 'NEW';
    t_prec(4) := 'SEA';
    t_prec(5) := null;
    t_nonstock('LAN') := t_prec;
    t_prec(1) := 'SEA';
    t_prec(2) := 'ROC';
    t_prec(3) := 'ATL';
    t_prec(4) := 'LAN';
    t_prec(5) := null;
    t_nonstock('NEW') := t_prec;
    t_prec(1) := 'ATL';
    t_prec(2) := 'NEW';
    t_prec(3) := 'LAN';
    t_prec(4) := 'SEA';
    t_prec(5) := null;
    t_nonstock('ROC') := t_prec;
    t_prec(1) := 'NEW';
    t_prec(2) := 'ROC';
    t_prec(3) := 'ATL';
    t_prec(4) := 'LAN';
    t_prec(5) := null;
    t_nonstock('SEA') := t_prec;
    t_prec(1) := 'ROC';
    t_prec(2) := 'LAN';
    t_prec(3) := 'ATL';
    t_prec(4) := 'SEA';
    t_prec(5) := 'NEW';
    t_nonstock('TOR') := t_prec;
    -- Body
    t_prec := t_nonstock(sku_rec.loc);
    t_val := test(t_prec,roc_stocking,lan_stocking,new_stocking,sea_stocking,atl_stocking);
    if t_val is not null then
    INSERT INTO stsc.temp_q_sku2
    SELECT * from stsc.sku
    where item = sku_rec.item
    and loc = t_val;
    else
    DELETE FROM stsc.temp_q_sku1
    WHERE CURRENT OF sku_cur;
    end if;
    end;

Posting Permissions

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


Click Here to Expand Forum to Full Width