Multiple DDL/queries at one time? (Error: Only a COMMIT WORK or null statement is legal after a DDL Statement)

Teradata Studio
Enthusiast

Multiple DDL/queries at one time? (Error: Only a COMMIT WORK or null statement is legal after a DDL Statement)

Hello everyone,

I’m new to Teradata’s tools, and I’m using Studio Express.  My coworkers are mostly using SQL Assistant, but I’m using SE because it’s the only Teradata product that’s offered for OSX.

(FYI, I believe I’m connecting in ANSI mode) I’m running into an issue where I can’t run most pre-existing queries, because I’m getting the same error over and over: “Only a COMMIT WORK or null statement is legal after a DDL Statement.” Most the queries we use involve multiple smaller queries, the general flow is something like this:

DROP staging.1

DROP staging.2

Etc

Etc

CREATE TABLE staging.1 AS

(SELECT

FROM

ETC)

CREATE TABLE staging.2 AS

(SELECT

FROM

INNER JOIN staging.1 ON user = user

WHERE etc)

ETC

ETC.

I’ve looked into this issue quite a bit, and it appears to be related to the connection mode I’m using (ANSI)? I’ve seen people say to use BT/ET (assuming they mean “BEGIN TRANSACTION/END TRANSACTION”), to use COMMIT statements, etc. I’ve tried the BEGIN/END, tried COMMIT statements, etc; nothing’s worked. I always get the “Only a COMMIT WORK or null statement is legal after a DDL Statement” error. Any help would be greatly appreciated!

Related bonus question: is there any way to drop a table, if it exists? Many of the existing queries that I’m using have sections at the beginning with multiple ‘Drop table A, drop table B, etc’ statements. It appears that SQL Assistant (or maybe just the BTEQ version of the queries) allows you to ignore errors thrown by attempting to drop tables, but Studio Express stops the entire statement from executing.

3 REPLIES
Teradata Employee

Re: Multiple DDL/queries at one time? (Error: Only a COMMIT WORK or null statement is legal after a DDL Statement)

In the Connection Profile, TMODE is probably ANSI by default. This can be changed to TERA and it should not require the COMMIT upon next logon, working as your coworkers' SQLA is probably working in Teradata mode. Alternatively, after each DDL you should be able to execute COMMIT. Another issue often seen when not-by-choice in ANSI mode is the SQL may not work as expected due to case-specific defaults. Unless ANSI is required, try TERA.

Teradata Employee

Re: Multiple DDL/queries at one time? (Error: Only a COMMIT WORK or null statement is legal after a DDL Statement)

There are several ways to run the SQL in the SQL Editor in Studio Express. The toolbar has two Execute buttons. The button with a single triangle (Execute SQL statements as individual statements) should do what you want. It will separate the statements in the editor into the individual statements and run them sequentially. If a statement fails, a message will be displayed asking if you want to stop running the statements or to continue.

The other button with two triangles (Execute all submitted as one statement) runs all of the statements as a single transaction. A transaction cannot have multiple DDL statements. Run this way with multiple DDL statements will result in a message in both ANSI and TERA modes (though the text of the message is different for the two modes).

If you are using the context menu in the editor, the "Execute All" corresponds to Execute all submitted as one statement and "Execute as Individual Statements" corresponds to button for running as individual statements.

Dropping multiple tables and continuing if any doesn't exist can be accomplished by running as individual statements (the only way multiple DROP statements can be run) and answering the dialog when the nonexistant table is encountered to just continue.

Enthusiast

Re: Multiple DDL/queries at one time? (Error: Only a COMMIT WORK or null statement is legal after a DDL Statement)

@Chuckbert, you astutely pointed out the 'execute as a single statement' vs 'execute all' options. This instantly clicked in my mind, with each statement running/getting committed sequentially. The 'execute as a single statement' option worked (I also found that my connection mode was ANSI, so I changed it to TERA).

Thanks for the help, all!