3932: Only an ET or null statement is legal after a DDL Statement.

Database
Enthusiast

3932: Only an ET or null statement is legal after a DDL Statement.

Hi All,

I am writing a code to change PI of an existing table.

  1. I am creating a backup table with new PI.
  2. Copying data from existing table
  3. Drop Old Table
  4. Rename backup table to original table.

Following is the code snippet : 

create table person_bck (person_id bigint not null , person_name varchar(100)) primary index (person_id);

insert into person_bck (person_id , person_name ) select person_id , person_name from person;

drop table person;

rename table person_bck to person;

This code executes fine but fails with the following error when i surround it with bt - et.

Statement 2: INSERT Failed. 3932:  Only an ET or null statement is legal after a DDL Statement. 

I want to write this code as part of a single transaction; if any of the statement fails, it rolls back everything.

Some posts suggested to commit after the create table command and end transaction. then do rest work in another transaction.

However, in that case the rollback of create table will not happend because its a separate unit altogether.

How can i achieve this in a single atomic transaction unit?

4 REPLIES
Enthusiast

Re: 3932: Only an ET or null statement is legal after a DDL Statement.

In BTET mode each individual statement is treated as a transaction, to combine multiple statements in transaction you need to use BTET. In case of DDL it is a requirement that each DDL statement must be followed by a COMMIT in case of ANSI mode. but in Teradata Mode I didn't find any such restriction. Might be changing the transaction mode will work for you. Else you will have to use the ET after each DDL to accomplish this. Or you can use some procedure to accomlish the task in entirety. 

Khurram
Teradata Employee

Re: 3932: Only an ET or null statement is legal after a DDL Statement.

Put

BT;

<UR LOGIC>

ET;

IF any thing fails in between.it rolls back

Enthusiast

Re: 3932: Only an ET or null statement is legal after a DDL Statement.

Saeed, i got the error in TD mode.

But, i got the point.

Thanks.

New Member

Re: 3932: Only an ET or null statement is legal after a DDL Statement.

After adding BT and ET , am facing the same error, Can u pls help me!