Conditional flow using simple If ELSE in BTEQ

Basic TEradata Query utility is one of the advantages of Teradata over other extraction utilities. The advantage of the BTEQ utility is its error handling ability and conditional execution. It is more common that the query execution is often compared against the conditional flow in programming languages and programmers may end up choosing JDBC connection to have control over the execution than writing scripts to achieve the same

While there are no direct if-else conditional statements in BTEQ, we can still achieve that with the help of ACTIVITYCOUNT and GOTO statements.

We have already seen the usage of ACTIVITYCOUNT in the other post. Now we will use GOTO along with that to perform conditional execution.

Conditional flow

if(customer in customerdb)
{
    get his details;
}
else
{
    insert into customerdb from another db;
    export the customer information from newdb to a file;
}

Conditional flow using BTEQ

.LOGON teradataserver/userid,password

SELECT 1 from custdb.customer where name='techieshouts';
.IF ERRORCODE > 0 THEN .EXIT 1;
.IF ACTIVITYCOUNT = 0 then GOTO LOAD_INFO;

.EXPORT file = <output_path of file>
.SET SEPARATOR '|'
 select name,city,country FROM custdb.customer where name='techieshouts';
.EXPORT reset;
.IF ERRORCODE > 0 THEN .EXIT 1;
.IF ACTIVITYCOUNT = 0 then .EXIT 2;

.LABEL LOAD_INFO;
INSERT INTO custdb.customer 
SELECT name,city,country FROM oldcustomerdb.customer;

.EXPORT file = <output_path of file>
.SET SEPARATOR '|'
 SELECT name,city,country FROM custdb.customer where name='techieshouts';
.EXPORT reset;
.IF ERRORCODE > 0 THEN .EXIT 1;
.IF ACTIVITYCOUNT = 0 then .EXIT 2;

.LOGOFF
EXIT 0;

Let us see the purpose of each of the above statements.

SELECT 1 FROM custdb.customer WHERE name='techieshouts';
.IF ERRORCODE > 0 THEN .EXIT 1;
.IF ACTIVITYCOUNT = 0 then GOTO LOAD_INFO;

In the above statement, first, we are selecting the customer information from the customer database. The ACTIVITYCOUNT here will tell if the record is already available or not. If the value is zero then we are sending the control to the label “LOAD_INFO”. This is the successful case of If condition.

.LABEL LOAD_INFO;
INSERT INTO custdb.customer 
SELECT name,city,country FROM oldcustomerdb.customer;
.EXPORT file = <output path>
.SET SEPARATOR '|'
SELECT name,city,country FROM custdb.customer WHERE  name='techieshouts';
.EXPORT reset;
.IF ERRORCODE > 0 THEN .EXIT 1;
.IF ACTIVITYCOUNT = 0 then .EXIT 2;

Here inside the If successful case, we are first inserting the data into the customerdb from the oldcustomerdb. Next, we are exporting the information from the customerdb to a file. After that, we are checking the ERRORCODE and ACTIVITYCOUNT to make sure the export is successful.

The else part here is the statements after the first select and before the .LABEL LOAD_INFO.

.EXPORT file = <output path>
.SET SEPARATOR '|'
SELECT name,city,country FROM custdb.customer WHERE name='techieshouts';
.EXPORT reset;
.IF ERRORCODE > 0 THEN .EXIT 1;
.IF ACTIVITYCOUNT = 0 then .EXIT 2;

This is the same as what we saw above. The regular flow to export the data to a file. The numbers given after the .EXIT keyword is the return code the will be given as the return code for the wrapper script

Finally, the flow is completed with the .logoff statement.

Also, check “Error handling with ACTIVITYCOUNT and ERRORCODE in BTEQ”, “BTEQ export to file without column header“