Dynamic SQL with Terdata

Database
Enthusiast

Dynamic SQL with Terdata

declare @Dyna_SQL nvarchar(4000)
select @Dyna_SQL =N'
DECLARE @commonid NUMERIC
SELECT @commonid = (SELECT id FROM common_table where loginname = ''system'')
DECLARE @maintable1 TABLE (OBJID1 NUMERIC, commonid1 NUMERIC)
INSERT INTO @maintable1 (OBJID1, commonid1 )
SELECT shopid, @commonid FROM shop_master where shopname like ''%music%''

DECLARE @maintable2 TABLE (OBJID2 NUMERIC, commonid2 NUMERIC)
INSERT INTO @maintable2 (OBJID2, commonid2 )
SELECT storeid, @commonid FROM store_master where storename like ''%Retail%''

select * from maintable1
union
select * from maintable2'

EXECUTE SP_EXECUTESQL @Dyna_SQL
===================
I am using some features in the above query which are propriety to MS SQL. Namely the Table variables and the Dynamic SQL (SP_EXCUTESQL).
Need to figure out how this can be achieved in Teradata. I cannot write a procedure for this because this is a dynamic sql and keeps changing.
Any help would be greatly appreciated.
10 REPLIES
Enthusiast

Re: Dynamic SQL with Terdata

As of now the only way of replicating the behavoiur is by having something like what I have pasted here under:

CREATE PROCEDURE PopulateTable1()
BEGIN
create volatile table maintable1 as
(

SELECT shopid, @commonid FROM shop_master where shopname like ''%music%''

)
with data
on commit preserve rows;
END;

CREATE PROCEDURE PopulateTable1()
BEGIN
create volatile table maintable2 as
(

SELECT storeid, @commonid FROM store_master where storename like ''%Retail%''

)
with data
on commit preserve rows;
END;

CREATE macro UNION_macro
as
(
select * from maintable1
union
select * from maintable2;

)

call PopulateTable1();
call PopulateTable2();
execute UNION_macro;

In my case I can have several table variables to be generated on the fly depending on the query requirement. I cant obviously be creating procedures on the fly and dropping them once the batch is run. The whole point of the exercise is that I need to run a string of SQL Statements in a batch which return me a recordset on completion. I have given an example of how I am doing it in SQL SERVER (the query is prepared on run time by the application depending on the user input).
Can this be done in Teradata as well?
Junior Contributor

Re: Dynamic SQL with Terdata

"the query is prepared on run time by the application depending on the user input"

Then there's no need for any Dynamic SQL within your example, just prepare a query/queries using plain SQL, e.g.:

SELECT shopid, dt.id
FROM shop_master, (SELECT id FROM common_table where loginname = 'system') dt
where shopname like '%music%'
union
SELECT storeid, dt.id
FROM store_master, (SELECT id FROM common_table where loginname = 'system') dt
where storename like '%Retail%';

The commonid will be calculated only once.

If you really need those table variables for further acccess you still can do your second approach but without SPs/Macros:
create volatile table maintable1 as
(

SELECT shopid, @commonid FROM shop_master where shopname like ''%music%''

)
with data
on commit preserve rows;

create volatile table maintable2 as
(

SELECT storeid, @commonid FROM store_master where storename like ''%Retail%''

)
with data
on commit preserve rows;

select * from maintable1
union
select * from maintable2;

Dieter
Enthusiast

Re: Dynamic SQL with Terdata

Thanks for replying.
I tried that query and it worked alright. Now to get the developers to try it from their code.
Enthusiast

Re: Dynamic SQL with Terdata

Heres an update on that one from my side.
The query worked well when I tried it from SQL Assistant, but when I put it in the code (VB .Net) it gave me the following error.
ErrorCode=-2146232009
Message="ERROR [25000] [NCR][ODBC Teradata Driver][Teradata Database] Only an ET or null statement is legal after a DDL Statement. "

For some reason the query seems to work using one environment (Query Assistant) but the exact query doesnot work when I fire it through code.
Junior Contributor

Re: Dynamic SQL with Terdata

You probably submit your query as a multistatement request, but within any request there might be only a single DDL statement

If you run it within QueryMan using F5 it's send step by step.
If you submit it using F9 it's send as one block and you'll probably get the same error in QueryMan, too.

You just have to submit seperate steps for each DDL when sending from .NET.

Dieter
Enthusiast

Re: Dynamic SQL with Terdata

Thanks for responding.

This one has landed me in loads of trouble. WOW!
Enthusiast

Re: Dynamic SQL with Terdata

Is there a possibility to create multiple volatile tables from an application is a single statement?
Enthusiast

Re: Dynamic SQL with Terdata

I think the answer is no. Volatile table contains DDL and multiple DDL statements cannot be submitted as a single statement. You may consider putting all the statments in a BTEQ script and run the script from your application. I am not sure how ( or whether it is possible) to run a BTEQ script from .Net.

Also, you may separate these staments and run from your application keeping the statements within the same session.
Enthusiast

Re: Dynamic SQL with Terdata

Thanks for the reply Somesh.

My case is indeed different because I have been using SP_ExecuteSQL (used for batch executions) in SQL Server which allowed me to create table variables (aka volatile tables in Teradata) and populate these with data in the same batch.
Since there are a lot of tables and different types of permutations and combinations possible, I cannot have these table variables pre-created and have to create them run-time.

This poses a unique challenge when I am porting my code to Teradata as the concept is not supported on it. The only option left is to have a generic structure form, which I can derive my Global Temporary tables or find out all the permutations and combinations possible and pre create the Global Temporary tables.

Appreciate the help I got on this forum.