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;