CODEWITHSUNDEEP
sqlwhile-loopconcatenationteradata
Barbara
Barbara

Reputation: 1168

WHILE loop in Teradata procedure

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:

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.

Upvotes: 0

Views: 20302

Answers (1)

dnoeth
dnoeth

Reputation: 60472

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

Upvotes: 2

Related Questions