Purpose
Used to conditionally execute portions of the input script. The IF clause can contain one or more conditions to enable testing multiple status values. These individual checks can be ANDed or ORed together, as well as be negated via NOT operations. When the collective test result is found to be TRUE, the THEN clause action gets taken.
The THEN clause can be comprised of an SQL request or another BTEQ command. Using GOTO as the command enables BTEQ to skip past one or more subsequent script instructions. This effectively provides a way to have "branch on error" logic to create error handling routine sections within input scripts.
Syntax
where the following is true:
- conditions
- One or more condition checks that can be nested. The base format for each check is a status value keyword compared to an integer within range for the status value. All of the status value keywords are valid for use. See Status Value Keywords for full descriptions.
- instruction
- An SQL request or one or more BTEQ commands.
Usage Notes
The IF...THEN... command is valid in a Teradata SQL macro, if the THEN clause includes a valid BTEQ command that is also valid in a Teradata SQL macro.
Example 1 – Branch on Database Error
To transfer control to the script section labelled as NXTREPORT when the error code generated by the last Teradata SQL request is 3000, enter the following command:
.IF ERRORCODE = 3000 THEN .GOTO NXTREPORT
Example 2 – Quit on No Rows Found
To set BTEQ’s return code to 1 if a Teradata SQL request finds no rows, enter the following command:
.IF ACTIVITYCOUNT = 0 THEN .QUIT 1
Example 3 – IF instigated by a macro
When used in a Teradata SQL macro, the IF...THEN... command has the following format:
ECHO '.IF ERRORCODE = 3000 THEN .REMARK ''Next'' ';
Example 4 – Detecting REPLACE PROCEDURE Compiler Errors
For automation purposes, there might be a need to detect when the REPLACE of a Stored Procedure has been successfully accomplished by the database yet the compiler used still reported errors. By default, the only way to determine if an otherwise successful compile resulted in errors is to check the WARNINGCODE status value. The resulting ERRORCODE would be 0. And the ACTIVITYCOUNT value would be the combined total number of warnings and errors. But, for an SQL-stored Procedure (SSP), a 5526 warning code can be explicitly tested for to see if the compiler gave an error. A 5527 would indicate it only gave warnings. However, its also possible that the compiler itself might not have been configured correctly to be able to perform a compile. To detect that, the ERRORCODE status value has to be checked. A single, multi-condition IF command can accomplish both tests. The following IF command will detect whether any type of error occurred for a COMPILE command:
.IF ((ERRORCODE!=0) OR (WARNINGCODE=5526)) THEN .GOTO errorpath
If the intent is to also detect compiler warnings, then the WARNINGCODE value just needs to be compared to 0:
.IF ( NOT ((ERRORCODE=0) AND (WARNINGCODE=0)) ) THEN .GOTO errorpath
Example 5 – Detecting a Missing IMPORT File
Some users consider a missing or empty import file scenario to be an error case and other users do not. One possible way to detect it as an error is to first use a .OS or .TSO command to issue a system call that is able to detect the file's state and then use an IF command to test the SYSTEMRETURNCODE status value. Here's the key instruction sequence used for UNIX BTEQs:
.OS test -e myfile.txt .IF SYSTEMRETURNCODE != 0 THEN .GOTO missingfile
And for Windows BTEQ using PowerShell:
.OS powershell "if ($false -eq (test-path -path myfile.txt)) {exit 1}"
.IF SYSTEMRETURNCODE != 0 THEN .GOTO missingfile