I wrote the below script using the while loop in a stored procedure against a dynamic SQL and is not producing the desired results. The codes in Order Table are horizontal vs lookup reference code table displays vertical Rsn’s codes. Is there any workaround? The code has no errors; however, one of the results is returned (3333 150).
Desired result:
OrderID MAIN_ID
1111 3
1111 104
2222 67
2222 162
3333 150
3333 67
4444 67
4444 48
4444 7
Code:
create PROCEDURE mydb.sp_test() SQL SECURITY CREATOR
BEGIN
DECLARE in_CurrRsnSK int;
DECLARE in_CurrErrDefID int;
DECLARE in_MaxSK int;
DECLARE in_Rsn1 smallint;
DECLARE in_Rsn2 smallint;
DECLARE in_Rsn3 smallint;
DECLARE in_CurrRsn smallint;
DECLARE in_NumMatches smallint;
DECLARE in_Type varchar(50);
DECLARE in_Code varchar(3);
DECLARE in_CurrIteration smallint ;
SET in_CurrRsnSK=1;
SET in_CurrErrDefID=1;
SET in_CurrIteration=1;
---------------------------------------------------------
drop table temp_order;
drop table temp_code;
drop table temp_Match;
drop table temp_Final;
---------------------------------------------------------
CREATE MULTISET VOLATILE TABLE temp_order
(OrderID int ,SubID int ,Code varchar(2)
,Rsn smallint ,RsnMatchFlag varchar(1)
) ON COMMIT PRESERVE ROWS;
---------------------
insert into temp_order (OrderID,SubID,Code,Rsn) values (1111,1,'A7',116);
insert into temp_order (OrderID,SubID,Code,Rsn) values (1111,2,'A8',496);
insert into temp_order (OrderID,SubID,Code,Rsn) values (1111,3,'A8',562);
insert into temp_order (OrderID,SubID,Code,Rsn) values (2222,1,'A3',247);
insert into temp_order (OrderID,SubID,Code,Rsn) values (2222,108,'A8',455);
insert into temp_order (OrderID,SubID,Code,Rsn) values (2222,109,'A8',255);
insert into temp_order (OrderID,SubID,Code,Rsn) values (2222,110,'A8',507);
insert into temp_order (OrderID,SubID,Code,Rsn) values (2222,111,'A8',455);
insert into temp_order (OrderID,SubID,Code,Rsn) values (2222,112,'A8',255);
insert into temp_order (OrderID,SubID,Code,Rsn) values (2222,113,'A8',507);
insert into temp_order (OrderID,SubID,Code,Rsn) values (3333,1,'A3',247);
insert into temp_order (OrderID,SubID,Code,Rsn) values (3333,2,'A8',228);
insert into temp_order (OrderID,SubID,Code,Rsn) values (3333,3,'A8',455);
insert into temp_order (OrderID,SubID,Code,Rsn) values (3333,4,'A8',507);
insert into temp_order (OrderID,SubID,Code,Rsn) values (4444,1,'A3',247);
insert into temp_order (OrderID,SubID,Code,Rsn) values (4444,2,'A6',145);
insert into temp_order (OrderID,SubID,Code,Rsn) values (4444,3,'A8',189);
insert into temp_order (OrderID,SubID,Code,Rsn) values (4444,4,'A8',249);
update temp_order set RsnMatchFlag=Null;
--------------------------
CREATE MULTISET VOLATILE TABLE temp_code
( SK int, Main_id int, Code varchar(2), Rsn1 smallint,Rsn2 smallint,Rsn3 smallint
) ON COMMIT PRESERVE ROWS;
insert into temp_code (SK,Main_id,Code,Rsn1,Rsn2,Rsn3) values (1,150,'A8',228,455,507);
insert into temp_code (SK,Main_id,Code,Rsn1,Rsn2,Rsn3) values (2,162,'A8',255,455,507);
insert into temp_code (SK,Main_id,Code,Rsn1,Rsn2,Rsn3) values (3,48,'A8',189,249,'');
insert into temp_code (SK,Main_id,Code,Rsn1,Rsn2,Rsn3) values (4,104,'A8',496,562,'');
insert into temp_code (SK,Main_id,Code,Rsn1,Rsn2,Rsn3) values (5,3,'A7',116,'','');
insert into temp_code (SK,Main_id,Code,Rsn1,Rsn2,Rsn3) values (6,7,'A6',145,'','');
insert into temp_code (SK,Main_id,Code,Rsn1,Rsn2,Rsn3) values (7,67,'A3',247,'','');
-- Check OrderID for each reason
/***************************************************************/
CREATE MULTISET VOLATILE TABLE temp_Match
( OrderID int ) ON COMMIT PRESERVE ROWS;
-------------------Final table with Order ID & Code Main id
CREATE MULTISET VOLATILE TABLE temp_Final
( OrderID int ,Main_id int ) ON COMMIT PRESERVE ROWS;
select MAX(sk) into in_MaxSK from temp_Code;
--loop starts
WHILE in_CurrRsnSK<= in_MaxSK
DO
BEGIN
-- Get Rsn
select (case when Rsn1=0 then -1 else Rsn1 end) into :in_Rsn1 from temp_Code where SK=in_CurrRsnSK ;
select (case when Rsn2=0 then -1 else Rsn2 end) into :in_Rsn2 from temp_Code where SK=in_CurrRsnSK ;
select (case when Rsn3=0 then -1 else Rsn3 end) into :in_Rsn3 from temp_Code where SK=in_CurrRsnSK ;
select Code into :in_Code from temp_Code where SK=in_CurrRsnSK ;
select Main_ID into :in_CurrErrDefID from temp_Code where SK=in_CurrRsnSK ;
SET in_NumMatches=((CASE WHEN in_Rsn1>0 THEN 1 ELSE 0 END) +
(CASE WHEN in_Rsn2>0 THEN 1 ELSE 0 END) +
(CASE WHEN in_Rsn3>0 THEN 1 ELSE 0 END));
-- Find Matching Status
SET in_CurrRsn=in_Rsn1;
WHILE in_CurrIteration<= 3 AND in_CurrRsn>0 DO
BEGIN
UPDATE TOr
FROM temp_order TOr
,( SELECT S1.OrderID, MIN(S1.Subid) AS SeqNo
FROM temp_order As S1
WHERE S1.Code=in_Code
AND S1.Rsn=in_CurrRsn
AND S1.RsnMatchFlag is null
GROUP BY S1.OrderID ) SQ
SET RsnMatchFlag='Y'
WHERE SQ.OrderID=TOr.OrderID
and SQ.SeqNo=TOr.Subid;
-- next reason
SET in_CurrIteration=in_CurrIteration + 1;
SET in_CurrRsn=(CASE WHEN in_CurrIteration=2 THEN in_Rsn2
WHEN in_CurrIteration=3 THEN in_Rsn3
ELSE -1 END);
END;
END WHILE;
-- Find Matching Claims
DELETE FROM temp_Match;
INSERT INTO temp_Match (OrderID)
SELECT OrderID
FROM temp_order
WHERE RsnMatchFlag='Y'
GROUP BY OrderID
HAVING COUNT(*)=in_NumMatches;
IF ACTIVITY_COUNT>0 THEN ---any matches/
BEGIN
-- Insert into the Final Table - Order / Main_id List
INSERT INTO temp_Final (OrderID, Main_id)
SELECT MATCH.OrderID ,in_CurrErrDefID
FROM temp_Match MATCH
LEFT JOIN temp_Final FN
ON FN.OrderID=MATCH.OrderID
aND FN.Main_id=in_CurrErrDefID
WHERE (FN.OrderID IS NULL or FN.OrderID = ' ');
-- Remove Code records that were used
DELETE FROM temp_order
WHERE temp_order.OrderID in (SELECT OrderID FROM temp_MATCH)
and temp_order.RsnMatchFlag='Y';
END; -- matches
END IF;
-- Clear any partial matches
UPDATE temp_order
SET RsnMatchFlag=NULL
WHERE RsnMatchFlag='Y';
-- Next reason
SET in_CurrRsnSK=in_CurrRsnSK + 1;
END;
END WHILE;
END ;
CALL mydb.sp_test();