Powerpivot - Teradata Query - Error 3932

Third Party Software
N/A

Powerpivot - Teradata Query - Error 3932

I am new to Teradata, so I am trying to learn all its quirks. What I am trying to accomplish is write a temp table and then query off of it all in one statement. When I run the query below in Powerpivot I get the following error:

[Teradata Database] [3932] Only an ET or null statement is legal after a DDL Statement.

If I run the same query in Teradata, it works if I use execute. If I use execute parallel, I get the same error. I am assuming that when it runs in parallel it is creating a circular reference. I am assuming the easiest solution would be to not run the query in parallel, but I don't see any way to accomplish this using Powerpivot. Any advise would be appreciated.

I miss using Microsoft SQL

CREATE VOLATILE TABLE CGCC
(
ID INT,
NAME VARCHAR(20)
)
ON COMMIT PRESERVE ROWS;

SELECT * FROM CGCC;
DROP TABLE CGCC

  • powerpivot
  • volatile table
1 REPLY

Re: Powerpivot - Teradata Query - Error 3932

Hi,

 

You've probably worked this out by now (or you're using Microsoft SQL Smiley Happy ), but for anyone else...

 

I'm guessing that this error code stems from the transaction mode being used by the PowerPivot connection to Teradata.

 

You'll probably find that PowerPivot is connecting a session to Teradata using ANSI transaction semantics. With this setting a transaction starts with the first SQL request and is not terminated unless you COMMIT, logoff or hit an error.

 

When a DDL request completes in an active transaction, the only acceptable other commands in that transaction are COMMIT, logoff or ABORT/ROLLBACK.

 

The other 'transaction semantics' option available to you is Teradata mode (aka BTET). By default in Teradata mode each request is a transaction, hence the 'CREATE TABLE' is automatically committed when it completes, terminating the transaction which then allows the SELECT request to run ok.

 

When you 'run it in Teradata' I assume that you mean using TD Studio or SQL Assistant. With either of those then 'by default' they will either auto-commit after each request if in ANSi mode or they will connect using Teradata mode.

 

In your Powerpivot connection settings/properties check for a parameter called TMODE or 'transaction mode' (or something similar). What it is called will depend on which API is being used (JDBC, ODBC, Net etc.).

 

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Tags (2)