Archives of the TeradataForum

Message Posted: Wed, 11 Mar 2009 @ 17:17:57 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

<-- Anonymously Posted: Wed, 11 Mar 2009 17:10 -->

Hi,

We have the Store Procedure (below) which errors with a 2620 in the highlighted area.

The data row it is trying to input into Systec.MVCanalysis is :-

     Exec Date Time = current_Timestamp(0)
     Databasename   = 'Sys_Reporting'
     Tablename      = 'qqq_logtable'
     Columnname     = 'accountname'
     xVal           =  $L$NOD   DBA  PERF   &D&H0&S
     xCnt           =  2
     xRnk           =  4

DDL for Systec.MVCAnalysis

     CREATE MULTISET TABLE systec.mvcanalysis ,NO FALLBACK ,
         NO BEFORE JOURNAL,
         NO AFTER JOURNAL,
         CHECKSUM = DEFAULT
         (
           ExecDateTime TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP(0),
           DatabaseName VARCHAR(128) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
           TableName VARCHAR(128) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
           ColumnName VARCHAR(128) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
           xVal VARCHAR(5000) CHARACTER SET LATIN NOT CASESPECIFIC,
           xCnt FLOAT NOT NULL,
           xRnk INTEGER NOT NULL)
        UNIQUE PRIMARY INDEX ( ExecDateTime ,DatabaseName ,TableName ,
           ColumnName ,xVal ,xCnt ,xRnk );

I know the problem is with the data value for xVal and the fact it contains a 2nd '$' sign, but I don't know why it is erroring and how to fix it.

The SQL that is generated works fine as a separate BTEQ - ie it inserts the row. So it must be something to do with the way the Store Procedure handles the data value .

Any help would be appreciated.

Stored Procedure:

     SPLText
     REPLACE PROCEDURE SYSTEC.MVCCOL_COUNT
        (OUT OUTPUT_MSG      VARCHAR(512))


        L1:BEGIN
        DECLARE DBNAME VARCHAR(128);
        DECLARE TBNAME VARCHAR(128);
        DECLARE CNAME VARCHAR(128);
        DECLARE CTYPE CHAR(2);
        DECLARE STUFF INTEGER;
        DECLARE MVCCUT BYTEINT;
        DECLARE STEPSQL VARCHAR(1024);
        DECLARE STEPDESC VARCHAR(512);
        DECLARE CFORMAT VARCHAR(30);
        DECLARE EXIT HANDLER FOR SQLEXCEPTION


        BEGIN


        SET OUTPUT_MSG = 'FAILURE ON '||STEPDESC||'. ERROR CODE: '||SQLCODE;


        END;

       ------------------------
        SET STEPDESC = 'VALIDATING ROWS TO PROCESS';
        ------------------------

            SELECT COUNT(*) INTO STUFF
            FROM SYSTEC.MVCREQTABLE
            WHERE COMP_STATUS = 'REQUESTED';

            IF STUFF = 0 THEN
            SET OUTPUT_MSG = 'NO REQUESTS TO PROCESS';
            LEAVE L1;
            END IF;

        UPDATE SYSTEC.MVCREQTABLE
        SET STARTDATETIME  = CURRENT_TIMESTAMP(0),
                 COMP_STATUS = 'SCHEDULED'
        WHERE COMP_STATUS = 'REQUESTED'
        ;


        DELETE FROM SYSTEC.MVCTABLECOUNTS ALL;

        FOR REQ_COUNT AS C_REQUEST_COUNT CURSOR FOR

        SELECT DATABASENAME, TABLENAME FROM SYSTEC.MVCREQTABLE GROUP BY 1,2

        DO

        SET DBNAME = REQ_COUNT.DATABASENAME;

        SET TBNAME = REQ_COUNT.TABLENAME;

        SET STEPSQL =
                 ' INSERT INTO SYSTEC.MVCTABLECOUNTS '
                 ' SELECT '''||DBNAME||''','
                 '          '''||TBNAME||''','
                 '            COUNT(*) '
        ' FROM '||DBNAME||'.'||TBNAME||''
        ' GROUP BY 1,2'
        ';';

        CALL DBC.SYSEXECSQL(:STEPSQL);

        END FOR;

        SET STEPSQL = '';

        ------------------------
        SET STEPDESC = 'SETTING VARIABLES FOR ANALYSIS';
        ------------------------
            L2:BEGIN

        FOR REQ_SET AS C_REQUEST CURSOR FOR

        SELECT DATABASENAME, TABLENAME, COLUMNNAME, COLUMNTYPE, MVCSAMPLE FROM SYSTEC.MVCREQTABLE

        DO

        SET DBNAME = REQ_SET.DATABASENAME;

        SET TBNAME = REQ_SET.TABLENAME;

        SET CNAME = REQ_SET.COLUMNNAME;

        SET CTYPE = REQ_SET.COLUMNTYPE;

        SET MVCCUT = REQ_SET.MVCSAMPLE;

        SET CFORMAT = '?';

        UPDATE SYSTEC.MVCREQTABLE
        SET COMP_STATUS = 'ANALYSING'
        WHERE COMP_STATUS = 'SCHEDULED'
        ;

        ------------------------
        SET STEPDESC = 'POPULATE  ANALYSIS TABLE';
        ------------------------

        IF CTYPE = 'DA'

        THEN

        SET CFORMAT =  '''YYYY-MM-DD''' ;

        END IF;

        IF CFORMAT <> '?' AND MVCCUT < 100

        THEN

        SET STEPSQL =

        ' LOCKING TABLE '||TRIM(DBNAME)||'.'||TRIM(TBNAME)||' FOR ACCESS'

           ' INSERT INTO SYSTEC.MVCANALYSIS'
               ' (DATABASENAME,'
               '  TABLENAME,'
               '  COLUMNNAME,'
               '  XVAL,'
               '  XCNT,'
               '  XRNK)'
               ' SELECT '''|| DBNAME || ''' ,'
           ' '''|| TBNAME || ''' ,'
           ' '''|| CNAME|| ''' AS CNAME,'
           '  XVAL,'
           '  XCNT,'

           ' RANK() OVER (PARTITION BY CNAME ORDER BY XCNT DESC) AS XRNK'

           ' FROM (SELECT  TOP '||MVCCUT||' PERCENT CAST(( ' ||CNAME || '(FORMAT '||CFORMAT||')) AS VARCHAR(5000)) AS XVAL,'
               ' COUNT(*) AS XCNT'
               ' FROM '||TRIM(DBNAME)||'.'||TRIM(TBNAME)||''
               ' GROUP BY 1) t1'
               ' QUALIFY XRNK < 256'
           ' ;';

           ELSEIF CFORMAT = '?' AND MVCCUT < 100 THEN

           SET STEPSQL =
        ' LOCKING TABLE '||TRIM(DBNAME)||'.'||TRIM(TBNAME)||' FOR ACCESS'

           ' INSERT INTO SYSTEC.MVCANALYSIS'
           ' (DATABASENAME,'
               '  TABLENAME,'
               '  COLUMNNAME,'
               '  XVAL,'
               '  XCNT,'
               '  XRNK)'
           ' SELECT '''|| DBNAME || ''' ,'
           ' '''|| TBNAME || ''' ,'
           ' '''|| CNAME|| ''' AS CNAME,'
           '  XVAL,'
           '  XCNT,'

           ' RANK() OVER (PARTITION BY CNAME ORDER BY XCNT DESC) AS XRNK'

           ' FROM (SELECT TOP '||MVCCUT||' PERCENT CAST( ' ||CNAME ||' AS VARCHAR(5000)) AS XVAL,'
              ' COUNT(*) AS XCNT'
              ' FROM '||TRIM(DBNAME)||'.'||TRIM(TBNAME)||''
              ' GROUP BY 1) t1'
              ' QUALIFY XRNK < 256'
           ' ;';

        ELSEIF CFORMAT =  '?' AND MVCCUT = 100 THEN

     ****      SET STEPSQL =
     ****   ' LOCKING TABLE '||TRIM(DBNAME)||'.'||TRIM(TBNAME)||' FOR ACCESS'
     ****
     ****      ' INSERT INTO SYSTEC.MVCANALYSIS'
     *      ' (DATABASENAME,'
     *          '  TABLENAME,'
     *          '  COLUMNNAME,'
     *          '  XVAL,'
     *          '  XCNT,'
     *          '  XRNK)'
     *      ' SELECT '''|| DBNAME || ''' ,'
     *      ' '''|| TBNAME || ''' ,'
     *      ' '''|| CNAME|| ''' AS CNAME,'
     *      ' CAST( ' ||CNAME || ' AS VARCHAR(5000)) AS XVAL,'
     *         ' COUNT(*) AS XCNT,'
     *
     *      ' RANK() OVER (PARTITION BY CNAME ORDER BY XCNT DESC) AS XRNK'
     *
     *      ' FROM '||TRIM(DBNAME)||'.'||TRIM(TBNAME)||''
     *         ' GROUP BY 4'
     *         ' QUALIFY XRNK < 256'
     ****      ' ;';

        ELSEIF CFORMAT <>  '?' AND MVCCUT = 100 THEN

        SET STEPSQL =

        ' LOCKING TABLE '||TRIM(DBNAME)||'.'||TRIM(TBNAME)||' FOR ACCESS'

        ' INSERT INTO SYSTEC.MVCANALYSIS'
        ' (DATABASENAME,'
            '  TABLENAME,'
            '  COLUMNNAME,'
            '  XVAL,'
            '  XCNT,'
            '  XRNK)'
        ' SELECT '''|| DBNAME || ''' ,'
        ' '''|| TBNAME || ''' ,'
        ' '''|| CNAME|| ''' AS CNAME,'
        ' CAST(( ' ||CNAME || '(FORMAT '||CFORMAT||')) AS VARCHAR(5000)) AS XVAL,'
            ' COUNT(*) AS XCNT,'

        ' RANK() OVER (PARTITION BY CNAME ORDER BY XCNT DESC) AS XRNK'

        ' FROM '||TRIM(DBNAME)||'.'||TRIM(TBNAME)||''
            ' GROUP BY 4'
            ' QUALIFY XRNK < 256'
        ' ;';


        END IF;



        CALL DBC.SYSEXECSQL(:STEPSQL);

        SET STEPSQL = '';
        END FOR;

        END L2;

        UPDATE SYSTEC.MVCREQTABLE
        SET COMP_STATUS = 'FINISHING'
        WHERE COMP_STATUS = 'ANALYSING'
        ;


        CALL SYSTEC.MVCREPORT();

        CALL SYSTEC.MVCVARREPORT();

        UPDATE SYSTEC.MVCREQTABLE
        SET COMP_STATUS = 'COMPLETED'
        WHERE COMP_STATUS = 'FINISHING'
        ;

            ------------------------
        SET STEPDESC = 'MVC ANALYSIS COMPLETED';
        ------------------------

        SET OUTPUT_MSG = 'MVC ANALYSIS COMPLETED ';

        END L1;

	<Prev	Next>		<<First	<Prev	Next>	Last>>