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]  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
ON COMMIT PRESERVE ROWS;
SELECT * FROM CGCC;
DROP TABLE CGCC
You've probably worked this out by now (or you're using Microsoft SQL ), 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.).
Were you able to find a solution for this issue? I am facing the same issue.
The solution presented bydoes not solve it.
Thanks a lot and kind regards,
Sorry to hear that, can you provide some more info about your processing?
Which transaction mode is your connection using? If not known, connect with Powerpivot and whilst logged on run the following:
SELECT USERNAME,sessionno,transaction_mode FROM dbc.sessioninfov WHERE USERNAME = '**bleep**';
In the above query replace "**bleep**" with your Teradata user name.
Can you provide the sql statements being run up to the point where this error occurs - if not too many.
Also, are you connecting with ODBC, JDBC etc.?
First of all, thanks for your reply.
I am connecting using Other Sources - > Other (OLEDB/ODBC) -> Microsoft OLE DB Provide for ODBC Drivers -> Teradata
I tried the following query on both session modes: ANSI and Teradata
create volatile table cpn11, no log as (
,trim(cpn.coupon_orig_cd)||trim(cpn.coupon_dest_cd) as od11
,cpn.dprt_dt as dprt11
,lof_list.oper_carr_cd as oper_carr11
,lof_list.mktg_carr_cd as mktg_carr11
,lof_list.tktng_cos as cos11
,lof_list.tkt_designator as des11
from co_prod_vmdb.vw_tcn_all_lof lof_list
left join co_prod_vmdb.vw_agency_current_with_hol agy
on agy.agency_id = lof_list.tktng_agency_id
left join co_prod_vmdb.vw_tcn_coupon cpn
on lof_list.record_id = cpn.record_id
and lof_list.relative_tkt_seq = cpn.relative_tkt_seq
and lof_list.coupon_num = cpn.coupon_num
lof_list.pos_country_cd in ('AR') and lof_list.tkt_stock_id = '016' and lof_list.relative_tkt_seq =1 and lof_list.coupon_num = 1 and lof_list.issued_dt between '2017-08-01' and '2017-08-01'
) with data primary index (initial_tkt_num) on commit preserve rows;
select * from cpn11
ERROR [HY000] [Teradata][ODBC Teradata Driver][Teradata Database] Only a COMMIT WORK or null statement is legal after a DDL Statement
ERROR  [Teradata][ODBC Teradata Driver][Teradata Database] Only an ET or null statement is legal after a DDL Statement.
Error when using "Teradata" mode session.
From the dbms perspective:
In Teradata mode, the default is that every request is a separate transaction and is implicitly comitted by the dbms. You/your application don't have to do anything.
The above default can be over-ridden by using BT/ET commands which sets up an explicit transaction.
In ANSI mode there is only one choice which is: the first request starts the transaction and the transaction is only terminated by a Failure, COMMIT, ABORT/ROLLBACK or Logoff.
And then you find out what the api level drivers are doing...
It looks like the TD ODBC driver supports the SQL_AUTOCOMMIT parameter.
I'm guessing that when you hit this error in Teradata mode:
- SQL_AUTOCOMMIT is set to false,
- the ODBC driver (or PowerPivot) is sending a 'BT' command before your CREATE TABLE.
- the following SELECT is then part of the same transaction as the CREATE TABLE - which is not allowed.
(we can get similar things happening when using the Teradata JDBC driver)
There needs to be an "ET" command after the CREATE TABLE command.
In ANSI mode there needs to be a COMMIT between the CREATE TABLE and the SELECT.
You can prove what SQL commands are being received by Teradata by using Query Logging. Trace all sql requests for your the relevant userid, you don't particularly need any of the other options for this process.
Can you check the connection settings for your Powerpivot setup? Do you have an 'auto commit' option (or something similar)?
Thanks. Nothing obvious (to me !) in there.
Are you able to view the SQL sent to Teradata? As per my earlier post you can use 'Query Logging' to do that. If you don't control this area you may need to speak to a DBA to see what is available and/or get it turned on for the userid that you're using.