Implementing iteration in BTEQ

Database
Enthusiast

Implementing iteration in BTEQ

Hi All,

 

I want to implement 'Iteration - Loop' logic in BTEQ, not using stored procedures.

Below is my attempt and example, in my temp. table, I will have the data/number of rows for iteration. If I have 3 rows, process for 3,2,1 and if I have 2 rows, process for 2,1 and so on. It is a static simulation for maximum of 3 iterations hardcoded.

 

Please give me a better way of doing the same in BTEQ, thank you in advance.

 

SELECT * FROM tmp_table1;

.IF ACTIVITYCOUNT = 3 THEN .GOTO LBL_3;

.IF ACTIVITYCOUNT = 2 THEN .GOTO LBL_2;

.IF ACTIVITYCOUNT = 1 THEN .GOTO LBL_1;

 

.LABEL LBL_3

SQL Statements

 

.LABEL LBL_2

SQL Statements

 

.LABEL LBL_1

SQL Statements

 

2 REPLIES
Junior Contributor

Re: Implementing iteration in BTEQ

You want to repeat exactly the same SQL statements 1, 2 or 3 times?

If they can be coded as a single Multi Statement Request this approach avoids repeating them in your code:

SELECT * FROM tmp_table1;
.IF ActivityCount = 0 THEN GOTO DontRun
.IF ActivityCount > 3 THEN GOTO DontRun

.IF ActivityCount = 3 THEN .REPEAT 3
.IF ActivityCount = 2 THEN .REPEAT 2

SQL Statement1
;SQL Statement2
;SQL Statement3

.LABEL DontRun

But why don't you want to use a Stored Procedure instead?

 

Senior Supporter

Re: Implementing iteration in BTEQ

You could also use the SELECT * FROM tmp_table1; to generate the SQLs you want to export into a file and run this file afterwards.

 

pseudocode:

.set width 200;

.set foldline on;

.export file = **bleep**.sql;

select 'your SQL1' (title ''),

'your SQL2' (title '')

from tmp_table1;

.export reset;

.run file = **bleep**.sql;

.exit 0;

 

This would work also for more then 3 repeats...