Teradata pass-through query insert into volatile table from WORK library table error

JediApprentice — Thu, 08 Nov 2018 18:20:10 GMT

I'm using the SAS/Teradata pass-through facility to create a volatile table and then insert data into it from a table in the WORK library. My work table has one field (my_var) and two rows with the values 'foo' and 'bar' called work.test_file. As you can see I use a global connection in the libref and connection strings. My query is as follows:

libname x teradata tdpid=myDBC authdomain="teraauth" mode=teradata connection=global; /* dbmstemp=yes */

proc sql;
  connect to teradata (tdpid=myDBC authdomain="teraauth" mode=teradata connection=global bulkload=yes);
  execute (CREATE VOLATILE TABLE my_vol_tbl (my_var char)
	       ON COMMIT PRESERVE ROWS
          ) by teradata;
quit;

proc sql;
  connect to teradata (tdpid=myDBC authdomain="teraauth" mode=teradata connection=global bulkload=yes);
  execute (INSERT INTO my_vol_tbl SELECT * FROM work.test_file) by teradata;
quit;

data final_test_tbl;
   set x.my_vol_tbl;
   put _all_;
run;

libname x clear;

The creation of the volatile table works. The error occurs under where I try to INSERT INTO my_vol_tbl SELECT * FROM work.test_file:

33 execute (INSERT INTO my_temp_tbl SELECT * FROM work.test_file) by teradata;
ERROR: Teradata execute: Syntax error, expected something like a name or a Unicode delimited identifier or an 'UDFCALLNAME' keyword
or '(' between the 'FROM' keyword and the 'work' keyword.

Thanks

Re: Teradata pass-through query insert into volatile table from WORK library table error

ChrisNZ — Thu, 08 Nov 2018 21:58:54 GMT

You are sending code to Teradata asking it to use table WORK.TESTFILE.

Teradata cannot see this table.

Try:

proc append base=X.MY_VOL_TBL data=WORK.TESTFILE;
run;

topic Re: Teradata pass-through query insert into volatile table from WORK library table error in SAS Programming

Teradata pass-through query insert into volatile table from WORK library table error

Re: Teradata pass-through query insert into volatile table from WORK library table error