Procedure for fastload cleanup

Applications
Applications covers the world of Teradata apps, including apps offered by Teradata (such as TRM or DCM), as well as best practices and methodologies for building your own Teradata-connected apps.
Enthusiast

Procedure for fastload cleanup

created  procedure for Fastload cleanup ( It will drop and recreate Landing table and also it will apply Table level comment also) and tested successfully.

 

Tested code in  sql assistant :

/*

CREATE TABLE DB.TBNAME AS

(

SELECT  ROW_NUMBER() OVER (

ORDER   BY COLUMNNAME  DESC ) AS SNO, COLUMNNAME , CAST( COMMENTSTRING AS VARCHAR(1000)) AS   COMMENTSTRING,

CAST('COMMENT ON ' || ' ' ||  'DBNAME' || '.' ||  'FLOAD_CLEANUP_TESTTBL'|| '.' || TRIM(A.COLUMNNAME) || ' ' ||  ' AS '|| '''' ||  TRIM(A.COMMENTSTRING) || ''';'  AS VARCHAR(10000) )AS COMMENTSTATEMENT

FROM     DBC.COLUMNSV A

WHERE   DATABASENAME='DBNAME'

                AND        TABLENAME='FLOAD_CLEANUP_TESTTBL'

                AND        COMMENTSTRING IS NOT NULL )

WITH       DATA NO PRIMARY INDEX ;

*/

 

 

 

/*

SET        ROW_CNT=0;

 

SELECT  COUNT(*) INTO :ROW_CNT

FROM       DBNAME.FLOAD_CLEANUP_TESTTBL_COMMENTS;

 

 

SET ROW_NUM=1;

 

WHILE ROW_NUM<=ROW_CNT

 

DO

 

 

CALL       DBC.SysExecSQL(SELECT COMMENTSTATEMENT FROM DBNAME.FLOAD_CLEANUP_TESTTBL_COMMENTS WHERE SNO=:ROW_NUM);

 

SET ROW_CNT=ROW_CNT+1;

 

END WHILE;

 

*/

 

/*

 

I have placed the above code in procedure like below but facing systax issues:

  

   SET SQL_TXT=SQL_TXT_COMMENT || TRIM(DB_NM) ||  '.' || TRIM(TBL_NM) || '_COMMENTS' || ' ' || 'AS (

 

SELECT ROW_NUMBER() OVER ( ORDER        BY COLUMNNAME  DESC ) AS SNO, COLUMNNAME ,

 

 CAST( COMMENTSTRING AS VARCHAR(1000)) AS   COMMENTSTRING,

 

  CAST('COMMENT ON ' || ' ' ||  TRIM(DB_NM) ||  '.' || TRIM(TBL_NM) || '.' || TRIM(A.COLUMNNAME) || ' ' ||  ' AS' || '''' || 

 

 TRIM(A.COMMENTSTRING) || ''';''  || ' ' || AS VARCHAR(10000) )AS COMMENTSTATEMENT

 

 

 

FROM     DBC.COLUMNSV A

WHERE UPPER(DATABASENAME)=UPPER(:DB_NM)

                AND        UPPER(TABLENAME) = UPPER(:TBL_NM)

                AND        COMMENTSTRING IS NOT NULL

               

                ) || ' ' || WITH DATA NO PRIMARY INDEX'  || ';' ;

 

Errors:

 

CALL Failed. 3760:  FLOAD_CLEANUP:String not terminated before end of text.

 

Syntax error: expected something between ';' and the word 'DBNAME'.

               

 Please help me on the above code.

2 REPLIES
Teradata Employee

Re: Procedure for fastload cleanup

DBC.SysExecSQL or EXECUTE IMMEDIATE requires a character string as the argument; you can't put a SELECT statement there.

Declare a cursor to loop through the table and fetch each row into a string variable, and execute that string as dynamic SQL within the loop.

Enthusiast

Re: Procedure for fastload cleanup

Please explain with eample...