-
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;
-
Where do the values of roc_stocking, lan_stocking etc come from?
-
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;
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|