WHILE loop in Teradata procedure

Question

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.

Looks like a MS SQL Server SP, there's no `+` to concat strings in Teradata, no `SPACE/CHAR`, the usage of the `OUT` parameter is wrong, lots of semicolons are missing. — dnoeth, Apr 24 '17 at 12:34
@dnoeth, thanks for your help. Could you clarify why the usage of the OUT parameter is wrong and where I'm missing semicolons? I'm new to procedures and I'm having a hard time finding documentation. — Barbara, Apr 24 '17 at 13:05
According to Standard SQL an OUT-parameter can only be set, but not read (you need to declare an intermediate variable which is finally assigned to the out var). — dnoeth, Apr 24 '17 at 14:43
`FROM tableWithSQLStmnts t ) D ( SqlStmnt )`: There's `, Seq` missing. But the `sysexecsql` will fail, too, because it can't run a Select (See http://stackoverflow.com/a/28638552) I would approach a task like this modifying the Select which inserts into `tableWithSQLStmnts` to return those `column, count(*)` and run it as a cursor instead simply appending the SQL string for all columns. — dnoeth, Apr 25 '17 at 10:08
@dnoeth I have now edited in that way but I'm still getting one error (or maybe more, but so far it's just showing one) — Barbara, Apr 25 '17 at 14:49
What's the data in `tableWithSQLStmnts`? Can you show some example? — dnoeth, Apr 25 '17 at 15:20
@dnoeth this is an example of the SQL in that table: SELECT 'columnname', CASE WHEN SUM(CNT) IS NULL THEN 0 ELSE SUM(CNT) END AS CntNull FROM ( SELECT 0 AS cnt FROM Sometable)a union all The statement in the last row is different because it doesn't end with union all. — Barbara, Apr 26 '17 at 06:47
Well, this Select is useless, it will always return `0`. If you want to count NULLs have a look at my edited answer :-) — dnoeth, Apr 26 '17 at 07:31

dnoeth · Accepted Answer · 2017-04-26T06:33:31.713

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');

thanks a lot for your answer @dnoeth. When I try to call it though I get this error: CALL Failed. [5531] Named-list is not supported for arguments of a procedure. tableWithSQLStmnts contains SQL statements that I got from a previous query and that I saved in a table. They are basically some select 'columnName', count(*) from Table with unions. So in that table I have a variable number of rows which depends on how many columns there are on the table I pass as a parameter. I need a single line because I need to put all the rows with those statements into one row and then run that cose. — Barbara, Apr 25 '17 at 06:57
EDIT to the previous post. I fixed that problem I still get another error -- I will edit the main post accordingly — Barbara, Apr 25 '17 at 07:25

WHILE loop in Teradata procedure

1 Answers1

Linked