To create a volatile table in crystal reports on terdata

UDA
N/A

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
4 REPLIES
N/A

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.

N/A

Re: To create a volatile table in crystal reports on terdata

Thanks Jimm for your reply..
I need to see if we can create global temp tables.
Is their any other solution from global temp tables..
N/A

Re: To create a volatile table in crystal reports on terdata

Yes.

If you can split your request in two, so that the Create is in one request from Crystal Reports, with the second request of Insert and Select.

The Global Temp is more efficient though.

Re: To create a volatile table in crystal reports on terdata

Hi 

Iam having the same issue as described above. GTT is not working for me.

Please let me know if some one has encountered a scenario like this.

Thanks

Vinod