Dynamic SQL Creation

[davedavedave]

Hoping someone can point me in the right direction. I need to dynamically create SQL and am not sure how to do this in teradata. Basically, I want to use the values of Column X in a table to be appended to the WHERE clause of my select statement by increments of 1000. Any help would be appreciated.

 

[CapsuleCorpJX]

http://www.teradata.com/t/go.aspx/index.html?id=117793

Annoyingly, you can't use SELECTS with dynamic sql.

However, you can:

Delete FROM <your temp table>;
SET tsql ='INSERT INTO <your temp table>
<your select statment>';
CALL DBC.SysExecSQL(tsql);

Then you can query the result from table you inserted into.
Make sure your table has the same number and type of columns as what you are selecting. I suggest using a global temp table.

However, your problem seems to be odd, I think you can do it without dyn sql.
Something like:
If you want it say the first 1000 values (sorted) from column X:
Select *
FROM <table1>
WHERE <table1>.<column1> IN (
SELECT X FROM <table2>
QUALIFY RANK(X) BETWEEN 1 AND 1000
)

If you want to just check for all values from column X:
Select *
FROM <table1>
WHERE <table1>.<column1> IN (
SELECT X FROM <table2>
)