Before I begin, I would like to mention that I have already searched all related topics and couldn't find an answer.
I have a SAP BODS script which is executing sql statements (going to teradata) that fails with the following error: Only an ET or null statement is legal after a DDL. What I am trying to do is the following (inside one BODS sql command):
1. Create Volatile table as select from another table
2. Insert data in the Volatile table
3. Insert data in table as select * from the Volatile table.
All of this works perfectly in SQL Assistant, but fails to run in my BODS script. The reason I want to use this arrangement is because the insert into the Volatile table takes a long time and I want to run parallel jobs that are inserting into the table in step 3. In other words I use the Volatile table as buffer to avoid locking the main table as the query takes long time. I understand that the problem is related to the driver session configuration, but I couldn't find any work around.
Any suggestion would be much appreciated.
It appears that BODS has wrapped your script call with BT; / ET; but Teradata requires DDL to commit or roll back before any other statement is allowed.
CREATE VOLATILE TABLE ...
ET; -- to satisfy Teradata
INSERT volatile ...
BT; -- to match up with BODS-generated ET;
INSERT target ...
Thank you for your suggestion. Unfortunately I got the same error. I tried removing everything apart of the Create vol. table and then inserting the data in it and I still got the same error. I checked the TD logs to see what gets submitted and it shows:
Create volatile table…
ET; (added by me)
BT; (added by me)
Insert into volatile table…
ET: (added by me – I tried without this ET as well, but no luck)