To create a volatile table in crystal reports on terdata
Hi I am migrating a set of reports from SQL Server 2000 to Terdata V12. My existing crystal report got a command in SQl server using Temp tables, so i am trying to create a volatile table in crystal reports on teradata database and i am getting this error [size=4]"Only an ET or null statement is legal after a DDL Statement"[/size]
Below is the code i am using
create volatile table test_data_vt ( capst_a_dt timestamp(6),adx_an_id integer,adx_br_id integer) primary index (capst_appn_dt, adx_appn_id, adx_bor_id) on commit preserve rows;
insert into test_data_vt select capst_a_dt,adx_an_id,adx_br_id from DADW.dms_ak where adx_appn_id=54625871;
select * from shaw_data_vt;
The Query is running fine in Teradata SQL Assistant and i am able to create the Volatile table
I am not able to solve the issuse, So please if any one can help me i really Appreciate
Re: To create a volatile table in crystal reports on terdata
If all this code is submitted in one request to the Teradata, it will run as an implicit transaction - hence you cannot run DML after the DDL Crerate Table. You can either split it up so that the Create table is done in one request (followed by another request for the Insert/ Select) or, if you can set up a global temporary table somewhere, you can use this.
You run once (I created the table in testdb - create it in a work database, although the space comes from your temp space - like a volatile table):
create Global Temporary table testdb.test_data_vt ( capst_a_dt timestamp(6),adx_an_id integer,adx_br_id integer) primary index (capst_a_dt, adx_an_id, adx_br_id) on commit preserve rows;
After this, the insert will create an instance of the table without the need for DDL.