Replacing huge IF statement with SQL code

[ssmith001]

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?

		
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;

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:

		
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;

[slimdave]

Where do the values of roc_stocking, lan_stocking etc come from?

[ssmith001]

This is in a PL/SQL loop:

            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;

[OraBis]

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.

[gamyers]

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;