|
|
|
Forum Newbie
Group: Forum Members
Last Login: 11/10/2009 3:17:17 PM
Posts: 2,
Visits: 9
|
|
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
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Yesterday @ 3:55:54 AM
Posts: 111,
Visits: 263
|
|
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.
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 11/10/2009 3:17:17 PM
Posts: 2,
Visits: 9
|
|
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..
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Yesterday @ 3:55:54 AM
Posts: 111,
Visits: 263
|
|
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.
|
|
|
|