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

Solution 1:

This is translated to valid syntax for Teradata/Standard SQL (and a bit simplified):

REPLACE PROCEDURE Test (OUT r2 VARCHAR(3000))

BEGINDECLARE RowCnt INT;
DECLARE i INT;

DECLARE CurrRow INT;
DECLARE r VARCHAR(3000);

SET CurrRow =1;
SET r ='SELECT ';
SET RowCnt = (SELECTCount(*) 
              FROM tableWithSQLStmnts
             );

WHILE CurrRow <= RowCnt DO
   BEGINSET r = r ||'MAX( CASE Seq WHEN '||Cast( CurrRow ASVARCHAR(10) ) ||' 
           THEN ''  '' || SqlStmnt
           ELSE '''' END )
   '||CASEWHEN 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))
DYNAMICRESULT SETS 1BEGINDECLARE QRY VARCHAR(3000);

   CREATE VOLATILE TABLE vt21(col VARCHAR(128) CHARACTERSET Unicode, NullCnt BIGINT) ONCOMMIT PRESERVE ROWS;

   SET QRY ='INSERT INTO vt21 ';

   FOR c ASSELECT DatabaseName, TableName, ColumnName, 
         Row_Number()
         Over (PARTITIONBY tablename
                ORDERBY columnname) AS rn,
         Count(*)
         Over (PARTITIONBY 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
        ||CASEWHEN c.rn = c.Cnt -- last rowTHEN';'ELSE' UNION ALL 'END;

   ENDFOR;

   EXECUTE IMMEDIATE QRY;

   BEGIN-- return the result setDECLARE resultset CURSORWITHRETURNONLYFOR S1;
      SET QRY ='SELECT * FROM  vt21;';
      PREPARE S1 FROM QRY;
      OPEN resultset;
   END;

   DROPTABLE vt21;

END;

CALL Test3('dbc', 'dbcinfoV');