How to execute multiple SQL statements in one database session?

KNIME Analytics Platform
1

I am using KNIME Desktop open source on top of a Teradata (TD) database.

Within TD it is possible to create volatile/temp tables. These are tables that only exisit in the user session and will automatically be dropped after the session closes (like the WORK lib in SAS).

This is very handy since in our scripts we use a lot of intermediary tables to get to the data we want. I have however no clue on how to execute these statements together in one db session from KNIME.

I have statement one for the volatile table:

CREATE VOLATILE TABLE temp_input AS
( SELECT ........ ) 
with data primary index (subscriber_id);

and the second statement :

CREATE TABLE flat_train AS
( SELECT ........ FROM temp_input) 
with data primary index (subscriber_id);

When I try to execute these through a database reader node I get the following error

ERROR     Database Reader                    Execute failed: [Teradata Database] [TeraJDBC 14.10.00.17] [Error 3932] [SQLState 25000] Only an ET or null statement is legal after a DDL Statement.

 

Any help is appreciated

 

2

The Database nodes in KNIME allow executing multiple SQL statements in one node for example inside the Database Reader. However, the last statement always need to be a pure SELECT-statement which ResultSet is returned as KNIME data table, for example adding SELECT * FROM flat_train should solve the KNIME issue. I also searched for the TD error code 3932 and it looks like that multiple TD statements need to be wrapped into a BT; ... ET; block.

3

Will try when back in the office after the holidays.... 

 

thanks

happy 2014