DDL Statement , Dynamic SQL in a FOR cursor LOOP -SQLCODE 3772

Database

DDL Statement , Dynamic SQL in a FOR cursor LOOP -SQLCODE 3772

I'm fairly new to teradata and stuck.

I'm using Teradata Studio. Connection is ANSI MODE - I believe this is significant.

It looks like I have noway to execute DDL statement as part of a FOR CURSOR LOOP. This can't be right (surely?).

I have stored Proc which creates Dynamic SQL to create a view. I have a few meta data tables to control the View created. I loop through the table to get the values I want for the view Creation statemnt.

Here's the problem. If I set up my test so the looop execute only ONCE then SUCCESS. EG if I call the stored proc like this

CALL BO_SIMPLE_VIEW_CREATOR ('ViewName' ,'2013-01-04'); It works fine.

However if I call the proc as part of a LOOP I get ---Failed [3722 : HY000] BO_SIMPLE_VIEW_WRAPPER:Only a COMMIT WORK or null statement is legal after a DDL Statement.

I have a COMMIT WORK outside the FOR LOOP. I seemingly can't put the COMMIT WORK inside the LOOP - Invalid.

The Outer Loop looks like this

  FOR REC_VIEW_CUR as C_VIEW_CURSOR

   CURSOR FOR SELECT VIEW_ID ,VIEW_NAME,Underlying_SCHEMA,Underlying_Table,Filtered_By_Date ,Fact_OR_Dimension ,Date_Filter_Column

        from  META_SIMPLE_VIEW_DESCRIPTION

        where  TARGET_SCHEMA = USER

        and  VIEW_IN_USE = 'Y'

        order by 1

    DO

     CALL BO_SIMPLE_VIEW_CREATOR(REC_VIEW_CUR.VIEW_NAME,'2013-01-04');

  END FOR;

  

 

 

 

 

The Loop Looks like this

  FOR REC_VIEW_CURSOR as C_VIEW_CURSOR

   CURSOR FOR SELECT VIEW_ID ,VIEW_NAME,Underlying_SCHEMA,Underlying_Table,Filtered_By_Date ,Fact_OR_Dimension ,Date_Filter_Column

        from  META_SIMPLE_VIEW_DESCRIPTION

        where  TARGET_SCHEMA = USER

        and  VIEW_IN_USE = 'Y'

        and  VIEW_NAME = :P_VIEW_NAME

        order by 1

    DO

     SET V_REPLACE_VIEW_TEXT = 'Replace View '|| REC_VIEW_CURSOR.VIEW_NAME||' ' ;

     SET V_SELECT_FROM  = REC_VIEW_CURSOR.Underlying_SCHEMA||'.'||REC_VIEW_CURSOR.Underlying_Table;

     SET V_VIEW_TARGET_COLUMN_LIST = '';

     SET V_VIEW_SOURCE_COLUMN_LIST = '';

     FOR  REC_COLUMNS as C_COLUMNS

       CURSOR FOR SELECT VIEW_ID,COLUMN_LIST_ID,VIEW_COLUMN_NAME,SOURCE_COLUMN_NAME,DERIVATION,Column_Order

          FROM META_SIMPLE_VIEW_COLUMN_LIST

             WHERE VIEW_ID = REC_VIEW_CURSOR.VIEW_ID and COALESCE(COLUMN_IN_USE,'Y') = 'Y' order by Column_Order

      DO  SET V_VIEW_TARGET_COLUMN_LIST = V_VIEW_TARGET_COLUMN_LIST || ','||REC_COLUMNS.VIEW_COLUMN_NAME;

       SET V_VIEW_SOURCE_COLUMN_LIST = V_VIEW_SOURCE_COLUMN_LIST || ','||COALESCE(REC_COLUMNS.DERIVATION, REC_COLUMNS.SOURCE_COLUMN_NAME);

     END FOR;

     -- Remove First ',' From Target and Source Lists

     SET V_VIEW_SOURCE_COLUMN_LIST = substring(V_VIEW_SOURCE_COLUMN_LIST FROM 2);

     SET V_VIEW_TARGET_COLUMN_LIST = substring(V_VIEW_TARGET_COLUMN_LIST FROM 2); 

     SET V_WHERE = CASE  WHEN COALESCE(REC_VIEW_CURSOR.Filtered_By_Date,'N')=  'N' THEN ''

           WHEN VDATE is NULL  THEN ''

           ELSE ' WHERE '|| REC_VIEW_CURSOR.Date_Filter_Column ||'>= '||''''||VDATE||''''

         END;  

     SET VSQL_TEXT = V_REPLACE_VIEW_TEXT||'('||V_VIEW_TARGET_COLUMN_LIST||')'||

           ' AS SELECT  '||V_VIEW_SOURCE_COLUMN_LIST||

           ' FROM '||V_SELECT_FROM||

           V_WHERE||

           ';';

     execute immediate VSQL_TEXT;

  END FOR;

COMMIT WORK;

2 REPLIES
Fan

Re: DDL Statement , Dynamic SQL in a FOR cursor LOOP -SQLCODE 3772

Did you ever get an answer to you question?

I too have a fairly simple loop with exececute immediate statements and the same error 3722.

Thanks.

Senior Apprentice

Re: DDL Statement , Dynamic SQL in a FOR cursor LOOP -SQLCODE 3772

There's no way to do this in an ANSI mode SP. 

If you can't switch to TD mode there's a workaround:

Don't EXECUTE the DDLs, but insert all of them into a Volatile Table adding a sequence number. Then run a second loop (based on the previous activity_count) to SELECT the individual DDL statements (WHERE seqence number = x) and EXECUTE/COMMIT each.

Dieter