Executing Dynamic SQL

[Terafic]

Hi
Can anyone give me some hints on how execute dynamic sql using SQL, Macros or Stored Procedures. I am generating SQL dynaically using SELECT, which I can store in a Table or pass as a string. But I have no idea how I can execute this SQL.
thanks in advance
Terafic!

 

[bengalliboy]

I am not clear how are you planing to run the 'SQL' or what platform are you runing... but you can try to execute the SQL as batch job. BTEQ is good tool for this...
If you are runing the jobs from UNIX platform, you can write a shell script and call the bteq job (bteq<file_name >logfile) and the Teradata utility should do it.

If you are using VB/Java or any other language you can first open a connection to the RDBC and Execute the SQL directly. You can also install the OLEDB/Teradata Utility for windows that include all the utilities like Bteq, multiload, Fastload etc and use them from windows.

Hope this hleps...

 

[Terafic]

Thanks for the reply...
What i am trying to do is create a SQL string using macros or stored procedures that incorporates paramter values as WHERE operators TABLE names and WHERE values. Once the SQL string is created and stored in a table, what facility can we use to execute it, without using C,JAVA or similar language.
The platform i am using is purely Teradata. In the ideal solution, SQL1 would grab SQL2 from a table and execute SQL2. The reason i cannot run SQL2 directly is that, SQL2 is not static. I would like to execute SQL1 from Queryman.

thanks

 

[techkid]

May be this would help - this is what i tried and it does work out. The solution is through BTEQ utility, but there are some limitation, read through.

Now that you say you have stored all the SQL generated strings in a table. You could write a bteq script as follows :
/*
.logon tdpid/user,passwd
.export report file=/filenameX
sel SQLstringcol from tablename;
.export reset
.run file=/filenameX skip 2
.logoff
.quit
*/

An explanation :
Line 1: Logon to the particular user
Line 2: The EXPORT command to dump the output of the following SQL statements till you encounter the &quot;export reset&quot; statement. thereby you specify the file where you want the output to be stored
Line 3:The statement which would get all your strings from the table into the file
Line 4:To end the exporting.
Line 5:To run the script that was generated in this case filenameX, the skip <number> is because to skip the column header and the line that follows if you open filenameX you will know what i mean - the first two line

Now to the limitation :
If the strings that you have constructed are more than 254 characters in length then you would have to use the &quot;REPORTWIDE&quot; option at Line 2 rather than the &quot;REPORT&quot; option. The 254 character is a limitation of bteq. Now to the interesting part the REPORTWIDE option has been knocked off from bteq versions above 5.4. So you need bteq version 5.4 or less where you have the REPORTWIDE option.
NOTE: the above is suggested only if the strings are longer than 254 characters.

Regarding executing the same through QueryManager, i cannot answer that because i have not used the QueryManager extensively with the options as above, perhaps you may find options similar to the ones i have specified above in Querymanager - do check it out.

Hope the solution solves the issue.

 

[tdatgod]

Hi,
Dynamic SQL is available via the Stored Procedure language in Teradata V2r4.1.

The following SQL statements cannot be used as dynamic
SQL in stored procedures:

- CALL
- CREATE PROCEDURE
- DATABASE
- EXPLAIN
- HELP
- REPLACE PROCEDURE
- SELECT
- SELECT - INTO
- SET SESSION ACCOUNT
- SET SESSION COLLATION
- SET SESSION DATEFORM
- SET TIME ZONE
- SHOW


The exact syntax can be found in

Chapter 4 of the Teradata RDBMS SQL Reference - Volume 6 Data Manipulation Statements

http://www.info.ncr.com/sd/sd-1.cfm?page=td

click on Sql Reference and then find Volume 6 of release 4.1.x.

Please let me know if you need

 

[techkid]

tdatgod,

Since you have mentioned about Dynmaic SQL execution through Stored procedure and the limitations - is there a way to overcome the limitation.
ie. Can i execute a query of the form
1. INSERT - SELECT
2. UPDATE - SELECT