stored-procedures - Teradata - 存储过程中的 While 循环不起作用
问题描述
我在针对动态 SQL 的存储过程中使用 while 循环编写了以下脚本，但没有产生预期的结果。订单表中的代码是水平与查找参考代码表显示垂直 Rsn 的代码。有什么解决方法吗？代码没有错误；但是，返回结果之一 (3333 150)。
期望的结果：
OrderID MAIN_ID
1111 3
1111 104
2222 67
2222 162
3333 150
3333 67
4444 67
4444 48
4444 7
代码：
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();
解决方案
推荐阅读
- android-layout - 我找不到动态添加的片段，片段管理器总是返回 null
- ios - 如果 Set 包含它，则更改数组内所有元素的变量
- rrdtool - RRD 工具 - 令人困惑的开始时间
- node.js - 将 Angular 更新到版本 8 后出现错误
- python - 在检查符号是 BTC 后，我想访问美元下的价格值
- php - 如何计算laravel中的订单总和？
- r - 如何使用 Matching 包中的 Mahalanobis 距离进行匹配
- python-3.x - 当其中一个容器挂载到文件系统时，如何连接两个容器？
- android - HashMap 不会写入和读取我想要的所有内容
- java - JpaRepositories 的 UnsatisfiedDependencyException