Automation - Cycle through lookup

Database
Fan

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).

8 REPLIES
Junior Contributor

Re: Automation - Cycle through lookup

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

Fan

Re: Automation - Cycle through lookup

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).

Fan

Re: Automation - Cycle through lookup

I need to run that interactively.

Enthusiast

Re: Automation - Cycle through lookup

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))

BEGIN

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 ;

EXECUTE IMMEDIATE sqlstr;

END;

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

Cheers,

Enthusiast

Re: Automation - Cycle through lookup

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,

Sri.

Junior Contributor

Re: Automation - Cycle through lookup

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;



Enthusiast

Re: Automation - Cycle through lookup

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

.logoff;

Please correct !!

Junior Contributor

Re: Automation - Cycle through lookup

= 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

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

and then run it in your script:

.run file endlessloop.sql;