Teradata过程中的WHILE循环 [英] WHILE loop in Teradata procedure
本文介绍了Teradata过程中的WHILE循环的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
在行号未知的情况下，我试图编写一个连接表中所有行的过程。
我有这段代码，但是它不管用。
创建过程测试（OUT r VARCHAR（3000））
BEGIN
DECLARE RowCnt INT;
DECLARE CurrRow INT;
SET CurrRow = 1，
r ='SELECT'，
RowCnt =（SELECT COUNT（*）
FROM tableWithSQLStmnts
）
其中CurrRow< = RowCnt DO
开始
SET r = r +
在CurrRow = 1
的情况下，然后'MAX（在CASE Seq When'+ CAST（CurrRow AS VARCHAR）+'
THEN SqlStmnt
ELSE SPACE（0）END）+'+ CHAR（13）
当i = RowCnt
THEN'MAX（CASE Seq何时'+ CAST（CurrRow AS VARCHAR）+'
THEN''''+ SqlStmnt
ELSE SPACE（0）END）'+ CHAR（13）
ELSE'MAX（CASE Seq WHEN'+ CAST（CurrRow AS VARCHAR）+'
THEN''''+ SqlStmnt
ELSE SPACE（0）END）+'+ CHAR（13）
END
SET CurrRow = CurrRow +1;
END；
SET r = r +'
FROM（SELECT SqlStmnt，
ROW_NUMBER（）OVER（Partition by TabName ORDER BY SQlStmnt）
FROM tableWithSQLStmnts t）D（SqlStmnt，Seq）
GROUP BY TabName;'
结束时；
END
;
我遇到以下错误：
- 语法错误，期望在整数和'，'之间出现';'之类的东西。
- 意外的文本'SET'。
dnoeth建议的新代码。
替换过程Test3（IN TbName VARCHAR（256））-，OUT r2 VARCHAR（3000））
开始
声明RowCnt INT;
DECLARE i INT;
DECLARE CurrRow INT;
DECLARE r VARCHAR（3000）;
DECLARE r2 VARCHAR（3000）;
SET CurrRow = 1;
SET r =‘SELECT’；
SET RowCnt =（SELECT COUNT（*）
FROM tableWithSQLStmnts
WHERE tabname =：TbName
）;
而CurrRow< = RowCnt DO
开始
SET r = r ||
'MAX（CASE Seq When'|| CAST（CurrRow AS VARCHAR（10））||'
THEN''，''|| SqlStmnt
ELSE''''END）
'
||当CurrRow = RowCnt
THEN’’
ELSE’时的案例|| ’
END;
SET CurrRow = CurrRow +1;
END；
结束时；
SET r = r || '
FROM（SELECT SqlStmnt，
ROW_NUMBER（）OVER（分区按TbName ORDER BY SQlStmnt）
FROM tableWithSQLStmnts t）D（SqlStmnt）
GROUP BY TbName
; ';
SET r2 = r;
CALL dbc.sysexecsql（：r）;
END；
现在我收到此错误：
[3706]语法错误：列名列表短于选择列表。
编辑2：
我现在有像这样重写它：
替换过程Test3（在TabName VARCHAR（256）中）
动态结果集1
开始
声明RowCnt INT；
DECLARE Seq INT;
DECLARE QRY VARCHAR（3000）;
DECLARE CurrRow INT;
SET QRY =’插入vt21选择’；
SET CurrRow = 1;
在提交的保留行上创建挥发表vt21（QRY VARCHAR（3000））;
SET RowCnt =（从测试表
中选择COUNT（*）
tabname =：TabName
）;
FOR CurrentRefRow AS SourceCursor光标
从TestTable
中选择SqlStmnt
当CurrRow< = RowCnt
DO
BEGIN
设置QRY = QRY ||
当CurrRow = 1时的情况下
THEN'MAX（CASE Seq When'|| CAST（CurrRow AS VARCHAR（10））||'
THEN''，''|| SqlStmnt
ELSE''''END）'
当CurrRow< RowCnt
THEN'，MAX（case Seq When'|| CAST（CurrRow AS VARCHAR（10））||'
THEN''，''|| SqlStmnt
ELSE'''' END）'
WHEN CurrRow = RowCnt
THEN'，MAX（CASE Seq When'|| CAST（CurrRow AS VARCHAR（10））||'
THEN''，''|| SqlStmnt
ELSE'''END）'
ELSE'|| ’
END;
SET CurrRow = CurrRow +1;
END；
结束时；
SET QRY = QRY || '
FROM（SELECT SqlStmnt，Tabname，
ROW_NUMBER（）OVER（PARTITION BY TabName ORDER BY SQlStmnt）
FROM TestTable t）D（Seq，Tabname，SqlStmnt）
GROUP BY TabName
;';
立即执行QRY；
结束；
开始-返回结果集
DECLARE结果集游标，仅对S1返回；
SET QRY =‘SELECT * FROM vt21;’;
从QRY准备S1；
OPEN结果集；
END；
DROP TABLE vt21;
END；
但是我遇到了以下错误：
呼叫失败。 [3813]位置分配列表的值太多。
我尝试对其进行修改，但是当我删除一个值时，它表示列名称列表比选择的列更长。列表。
解决方案
这已翻译为Teradata / Standard SQL的有效语法（并做了一些简化）：
替换过程测试（OUT r2 VARCHAR（3000））
开始
声明RowCnt INT;
DECLARE i INT;
DECLARE CurrRow INT;
DECLARE r VARCHAR（3000）;
SET CurrRow = 1;
SET r =‘SELECT’；
SET RowCnt =（SELECT Count（*）
FROM tableWithSQLStmnts
）;
而CurrRow< = RowCnt DO
开始
SET r = r ||
'MAX（CASE Seq When'|| Cast（CurrRow AS VARCHAR（10））||'
THEN''''|| SqlStmnt
ELSE''''END）
'
||当CurrRow = RowCnt
THEN’’
ELSE’时的案例|| ’
END;
SET CurrRow = CurrRow +1;
END；
结束时；
SET r = r || '
FROM（SELECT department_name--SqlStmnt，
ROW_NUMBER（）OVER（PARTITION BY TabName ORDER BY SQlStmnt）
FROM tableWithSQLStmnts t）D（SqlStmnt，Seq）
GROUP BY TabName
;';
SET r2 = r;
END
；
tableWithSQLStmnts 的内容是什么？ p>
为什么要单行？有一种更简单的方法来获取 LISTAGG 。
编辑：
根据您的评论（在此处以及在Teradata的Developer Exchange上），您似乎想对每列应用某种计数。但是然后您就不需要 MAX / CASE / ROW_NUMBER ，只需将一个表的所有行连接起来然后执行即可。这将计算表的每一列中的NULL：
替换过程Test3（IN DBName VARCHAR（128），IN TabName VARCHAR（128 ））
动态结果集1
开始
DECLARE QRY VARCHAR（3000）;
在提交的保留行上创建挥发表vt21（col VARCHAR（128）字符集Unicode，NullCnt BIGINT）；
SET QRY =‘INSERT INTO vt21’；
FOR c AS
选择DatabaseName，TableName，ColumnName，
Row_Number（）
Over（按表名划分
按列名排序）AS rn，
Count（*）
以上（按表名划分）AS Cnt
从dbc.ColumnsV
WHERE DatabaseName =：DBName
AND TableName =：TabName
DO
SET QRY = QRY
|| ‘SELECT’’’|| c.ColumnName
|| ’’’，COUNT（出现时的情况|| c。列名
||’IS NULL THEN 1 END）来自’
|| c.DatabaseName || ’。’|| c.TableName
|| c.rn = c.Cnt时的情况-最后一行
THEN’；’
ELSE’UNION ALL’
END;
结束；
立即执行QRY；
BEGIN-返回结果集
DECLARE结果集游标，仅对S1返回；
SET QRY =‘SELECT * FROM vt21;’;
从QRY准备S1；
OPEN结果集；
END；
DROP TABLE vt21;
END；
CALL Test3（ dbc， dbcinfoV）；
I'm trying to write a procedure that concatenates all rows in a table in the case in which the row number is unknown.
I have this code but it is not working.
CREATE PROCEDURE Test (OUT r VARCHAR(3000))
BEGIN
DECLARE RowCnt INT;
DECLARE CurrRow INT ;
SET CurrRow = 1,
r = 'SELECT ',
RowCnt = (SELECT COUNT(*)
FROM tableWithSQLStmnts
)
WHILE CurrRow <= RowCnt DO
BEGIN
SET r = r +
CASE WHEN CurrRow = 1
THEN 'MAX( CASE Seq WHEN ' + CAST( CurrRow AS VARCHAR ) + '
THEN SqlStmnt
ELSE SPACE(0) END ) + ' + CHAR(13)
WHEN i = RowCnt
THEN 'MAX( CASE Seq WHEN ' + CAST( CurrRow AS VARCHAR ) + '
THEN '' '' + SqlStmnt
ELSE SPACE(0) END ) ' + CHAR(13)
ELSE 'MAX( CASE Seq WHEN ' + CAST( CurrRow AS VARCHAR ) + '
THEN '' '' + SqlStmnt
ELSE SPACE(0) END ) + ' + CHAR(13)
END
SET CurrRow = CurrRow + 1 ;
END ;
SET r = r + '
FROM ( SELECT SqlStmnt,
ROW_NUMBER() OVER ( PARTITION BY TabName ORDER BY SQlStmnt )
FROM tableWithSQLStmnts t ) D ( SqlStmnt, Seq )
GROUP BY TabName;'
END WHILE;
END
;
I'm getting the following errors:
- Syntax error, expected something like ';' between an integer and ','.'.
- Unexpected text 'SET'.
New code, as suggested by dnoeth.
REPLACE PROCEDURE Test3 (IN TbName VARCHAR(256)) --, OUT r2 VARCHAR(3000))
BEGIN
DECLARE RowCnt INT;
DECLARE i INT;
DECLARE CurrRow INT;
DECLARE r VARCHAR(3000);
DECLARE r2 VARCHAR(3000);
SET CurrRow = 1;
SET r = 'SELECT ';
SET RowCnt = (SELECT COUNT(*)
FROM tableWithSQLStmnts
WHERE tabname = :TbName
);
WHILE CurrRow <= RowCnt DO
BEGIN
SET r = r ||
'MAX( CASE Seq WHEN ' || CAST( CurrRow AS VARCHAR(10) ) || '
THEN '' , '' || SqlStmnt
ELSE '''' END )
'
|| CASE WHEN CurrRow = RowCnt
THEN ''
ELSE ' || '
END;
SET CurrRow = CurrRow + 1 ;
END;
END WHILE;
SET r = r || '
FROM ( SELECT SqlStmnt,
ROW_NUMBER() OVER ( PARTITION BY TbName ORDER BY SQlStmnt )
FROM tableWithSQLStmnts t ) D ( SqlStmnt )
GROUP BY TbName
;';
SET r2 = r;
CALL dbc.sysexecsql(:r);
END;
Now I get this error:
[3706] Syntax error: Column name list shorter than select list.
EDIT 2:
I have now rewritten it like this:
REPLACE PROCEDURE Test3 (IN TabName VARCHAR(256))
DYNAMIC RESULT SETS 1
BEGIN
DECLARE RowCnt INT;
DECLARE Seq INT;
DECLARE QRY VARCHAR(3000);
DECLARE CurrRow INT;
SET QRY= 'INSERT INTO vt21 SELECT ';
SET CurrRow = 1;
CREATE VOLATILE TABLE vt21(QRY VARCHAR(3000)) ON COMMIT PRESERVE ROWS;
SET RowCnt = (SELECT COUNT(*)
FROM TestTable
WHERE tabname = :TabName
);
FOR CurrentRefRow AS SourceCursor CURSOR FOR
SELECT SqlStmnt
FROM TestTable
DO
WHILE CurrRow <= RowCnt
DO
BEGIN
SET QRY = QRY ||
CASE WHEN CurrRow=1
THEN 'MAX( CASE Seq WHEN ' || CAST( CurrRow AS VARCHAR(10) ) || '
THEN '' , '' || SqlStmnt
ELSE '''' END ) '
WHEN CurrRow < RowCnt
THEN ', MAX( CASE Seq WHEN ' || CAST( CurrRow AS VARCHAR(10) ) || '
THEN '' , '' || SqlStmnt
ELSE '''' END ) '
WHEN CurrRow=RowCnt
THEN ', MAX( CASE Seq WHEN ' || CAST( CurrRow AS VARCHAR(10) ) || '
THEN '' , '' || SqlStmnt
ELSE '''' END ) '
ELSE ' || '
END;
SET CurrRow = CurrRow + 1 ;
END;
END WHILE;
SET QRY = QRY || '
FROM ( SELECT SqlStmnt, Tabname,
ROW_NUMBER() OVER ( PARTITION BY TabName ORDER BY SQlStmnt )
FROM TestTable t ) D ( Seq, Tabname, SqlStmnt )
GROUP BY TabName
;';
EXECUTE IMMEDIATE QRY;
END FOR;
BEGIN -- return the result set
DECLARE resultset CURSOR WITH RETURN ONLY FOR S1;
SET QRY = 'SELECT * FROM vt21;';
PREPARE S1 FROM QRY;
OPEN resultset;
END;
DROP TABLE vt21;
END;
But I'm getting the following error:
CALL Failed. [3813] The positional assignment list has too many values.
I have tried modifying it but when I delete one value than it says that column name list is longer then the select list.
解决方案
This is translated to valid syntax for Teradata/Standard SQL (and a bit simplified):
REPLACE PROCEDURE Test (OUT r2 VARCHAR(3000))
BEGIN
DECLARE RowCnt INT;
DECLARE i INT;
DECLARE CurrRow INT;
DECLARE r VARCHAR(3000);
SET CurrRow = 1;
SET r = 'SELECT ';
SET RowCnt = (SELECT Count(*)
FROM tableWithSQLStmnts
);
WHILE CurrRow <= RowCnt DO
BEGIN
SET r = r ||
'MAX( CASE Seq WHEN ' || Cast( CurrRow AS VARCHAR(10) ) || '
THEN '' '' || SqlStmnt
ELSE '''' END )
'
|| CASE WHEN CurrRow = RowCnt
THEN ''
ELSE ' || '
END;
SET CurrRow = CurrRow + 1 ;
END;
END WHILE;
SET r = r || '
FROM ( SELECT department_name--SqlStmnt,
ROW_NUMBER() OVER ( PARTITION BY TabName ORDER BY SQlStmnt )
FROM tableWithSQLStmnts t ) D ( SqlStmnt, Seq )
GROUP BY TabName
;';
SET r2 = r;
END
;
What's the content of tableWithSQLStmnts?
Why do you want a single line? There are simpler ways to get a kind of LISTAGG.
Edit:
Based on your comments (here and on Teradata's Developer Exchange) it looks like you want to apply some kind of count to every column. But then you don't need the MAX/CASE/ROW_NUMBER, simply concat all rows for a table and then execute it. This counts NULLs in every column of a table:
REPLACE PROCEDURE Test3 (IN DBName VARCHAR(128),IN TabName VARCHAR(128))
DYNAMIC RESULT SETS 1
BEGIN
DECLARE QRY VARCHAR(3000);
CREATE VOLATILE TABLE vt21(col VARCHAR(128) CHARACTER SET Unicode, NullCnt BIGINT) ON COMMIT PRESERVE ROWS;
SET QRY = 'INSERT INTO vt21 ';
FOR c AS
SELECT DatabaseName, TableName, ColumnName,
Row_Number()
Over (PARTITION BY tablename
ORDER BY columnname) AS rn,
Count(*)
Over (PARTITION BY tablename) AS Cnt
FROM dbc.ColumnsV
WHERE DatabaseName = :DBName
AND TableName = :TabName
DO
SET QRY = QRY
|| 'SELECT ''' || c.ColumnName
|| ''', COUNT(CASE WHEN ' || c.columnname
|| ' IS NULL THEN 1 END) FROM '
|| c.DatabaseName || '.' || c.TableName
|| CASE WHEN c.rn = c.Cnt -- last row
THEN ';'
ELSE ' UNION ALL '
END;
END FOR;
EXECUTE IMMEDIATE QRY;
BEGIN -- return the result set
DECLARE resultset CURSOR WITH RETURN ONLY FOR S1;
SET QRY = 'SELECT * FROM vt21;';
PREPARE S1 FROM QRY;
OPEN resultset;
END;
DROP TABLE vt21;
END;
CALL Test3('dbc', 'dbcinfoV');
这篇关于Teradata过程中的WHILE循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
