All Forums Database
Poddy 3 posts Joined 01/14
27 Jan 2014
Automation - Cycle through lookup

Hello All!
Got a tricky situlation, heres the problem.
I need to cycle through a look up table (A), which refers to a list of IDs. Using 1 ID at a time I would like to do joins etc refering to that ID.
After thats complete, store the info into a new table and cycle to the next in the list and repeat.
I've been looking at GOTO & ACTIVITYCOUNT but am unable to go back in my code as it tends to only read down.
The purpose of doing this as I have spoil space restrictions and am dealing with over 100 Millions records.
Thanks in advance for your time, any examples would be great! I'm using Version 14 and don't have permissions for Stored prodedures! (which complicates this further).

dnoeth 4628 posts Joined 11/04
27 Jan 2014

I would suggest you better talk to your DBA to get more spool first.
You can't use GOTO to go back within a script.
Do you need to run that in a batch or interactively?
What about following approach?
- insert the data from the lookup table in a Volatile Table
- join to a single value (SELECT MAX(lookupvalue) FROM vt)
- DELETE FROM vt WHERE lookupvalue = (SELECT MAX(lookupvalue) FROM vt);
- Repeat it until activitycount = 0


Poddy 3 posts Joined 01/14
28 Jan 2014

Hi Dieter,
Thanks for getting back to me.
Thats was my plan, but am getting stuck on the 'Repeat' part, as I would need to copy & paste the code serveral times where I'm unable to cycle backwards using 'GOTO'.
Copy & Paste wouldn't be such an issue if it was a static table but it will grow every week, so would prefer as little maintance as possible.
I've requested the use of stored procedures, will this simplify the problem? (I'm not too familiar with them in Teradata but do have programming experiance).

Poddy 3 posts Joined 01/14
28 Jan 2014

I need to run that interactively.

Raja_KT 1246 posts Joined 07/09
28 Jan 2014

With stored procedure, you have more liberty and have more options like branching , if then else, looping...for loop, do while,while ..etc. Also you can stuff things like what Dieter explained. You can delete the finished part.
sample proc:

CREATE PROCEDURE new_sales_table (my_table VARCHAR(30),

my_database VARCHAR(30))


DECLARE sales_columns VARCHAR(128)

DEFAULT '(item INTEGER, price DECIMAL(8,2) ,

sold INTEGER)' ;

DECLARE sqlstr VARCHAR(500);

SET sqlstr = 'CREATE TABLE ' || my_database ||

'.' || my_table || sales_columns ;



or CALL DBC.SysExecSQL('CREATE TABLE ' || my_database ||'.' || my_table || sales_columns) ;



Raja K Thaw
My wiki:
Street Children suffer not by their fault. We can help them if we want.

sgarlapa 88 posts Joined 03/13
28 Jan 2014

Hi Dieter,
I got a question in your explained approach.
I know we can repeat statement for particular number times or with * till end of the imported file.
Can we repeat a set of statments till activity_count =0 ?
Thank you,

dnoeth 4628 posts Joined 11/04
29 Jan 2014

Hi Sri,
you can't repeat until activitycount = 0 (in BTEQ, of course you easily can in a SP).
If you know about the maximum number of levels in a BTEQ script you could do the SELECT/DELETE as a Multi Statement Request and the use =1 instead of repeat:

(SELECT MAX(lookupvalue) FROM vt)

;DELETE FROM vt WHERE lookupvalue = (SELECT MAX(lookupvalue) FROM vt)


.if activitycount = 0 then =1;

.if activitycount = 0 then =1;

.if activitycount = 0 then =1;

.if activitycount = 0 then =1;




sgarlapa 88 posts Joined 03/13
31 Jan 2014

It is a new info for me. So, if we use "then =1"  would it run the same statment again if activitycount =0?
in this case it is a MSR so it would run all the statments again and again utill activitycount become <> 0.
I just have reshuffled your staments as per current requirement can you please confrim !!
.logon 1/id,pswd;
create volatile table with all lookupvalues;
--MSR starts
select max(lookupvalues) from vt and join with a big table
;DELETE FROM vt WHERE lookupvalue = (SELECT MAX(lookupvalue) FROM vt)
.if activitycount <> 0 then =1;
--with this condition the above MSR runs again untill it is --vt become empty
Please correct !!

dnoeth 4628 posts Joined 11/04
31 Jan 2014

= x is not running until activitycount <> 0, it is repeating the previous request x times.
When you know about the number of repetititons you can simply repeat that line of code x times.
You can also abuse the run command, but this might be dangerous when you go into an endless loop, you've been warned:
 Create a file name endlessloop.sql with following content

.if activitycount > 0 then .run file endlessloop.sql;

and then run it in your script:

.run file endlessloop.sql;



You must sign in to leave a comment.