1768795 - Error: "Failed to retrieve data from database. Details: 25000:[Teradata][ODBC Teradata Drives][Teradata Database] Only an ET or Null statement is legal after DDLstatement.[Database Vendor Code:-3932]" in crystal Reports calling Teradata Volatile tables using command object

1. Custom SQL calling Teradata volatile tables are failing with database error in Crystal Reports.
2. Unable to use "Command Object" calling Teradata Volatile tables in Crystal Report. Same SQL is working when directly executed at database end.

• Crystal Reports 2008
• Teradata

1. Create a new connection in Crystal Reports connecting to Teradata database.
2. Select "Add Command" option and add the code to call volatile table. (Sample command is given below)
3. Click OK
4. Crystal reports would display "Failed to retrieve data....[Database Vendor Code:-3932]" error

Create volatile table test_vt
( Field_1 integer,Field_2 integer)

primary index (Field1,Field2)
on commit preserve rows

insert into test_vt
(1,2)

 Select * from test_vt

In order to query from volatile table is requires DDL (create table)followed by DML (Insert). If the code is submitted in one request to the Teradata, it will run as an implicit transaction and as you cannot run DML after the DDL, query would fail. 

This is default behavior from Crystal Reports designer as Crystal Reports do not handle Volatile tables differently.

Workaround1

Instead of using Volatile table create a global temporary table in the database. Similar to Volatile tables, its space would come from temp space.It would require create table command to be run only once at database end after which we can simply call DML(Insert statement) from Crystal Reports without requiring DDL to be included in the code.

Workaround2

Create a stored procedure to execute the custom code calling volatile table. Then create reports on stored procedure.

DDL, DML, Teardata, Volatile tables. , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem