Is there a way to tweak ODBC/Stata to work with volatile tables in Linux?
Stata (a statistics package) allows SQL statements to be issued directly to ODBC data source. I believe SAS has similar pass-thorugh functionality. There's some description of this here: http://www.stata.com/help.cgi?odbc
Here's an example of a simple toy SQL query that I am using in Stata:
odbc load, exec("create volatile table new_usr as (select top 10 user_id from dw_users) with data primary index(user_id) on commit preserve rows; select * from new_usr;") clear dsn("mozart") lowercase;
This is the error message I am getting:
The ODBC driver reported the following diagnostics [Teradata][ODBC Teradata Driver][Teradata Database] Only an ET or null statement is legal after a DDL Statement. SQLSTATE=25000 r(682);
I have tried altering session mode in the .odbc.ini to ANSI and adding a "commit work;" statament. I also tried using TERADATA mode with BT and ET statements. I also added CONNECTION=GLOBAL to the .odbc.ini entry for the DSN.
I have also tried separating the create and the select stataments, but then Stata complains the the volatile table does not exits.