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.
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?
"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;
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  [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.
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.
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.