IF, ELSEIF, ELSE, and ENDIF - Basic Teradata Query

Basic Teradata Query Reference

Product
Basic Teradata Query
Release Number
16.20
Published
October 2018
Language
English (United States)
Last Update
2020-02-20
dita:mapPath
kil1527114222313.ditamap
dita:ditavalPath
Audience_PDF_include.ditaval
dita:id
B035-2414
lifecycle
previous
Product Category
Teradata Tools and Utilities

Purpose

The IF, ELSEIF, ELSE and ENDIF commands are used to conditionally execute portions of the input script. An IF command can be used by itself to make a single instruction (SI) conditional by using a THEN clause. Or it can be used without a THEN clause in combination with the ELSEIF, ELSE and ENDIF commands to make multi-instruction (MI) blocks conditional. Minimally, an MI IF construct must start with an IF command and end with an ENDIF command.

An explicitly specified set of one or more condition checks that test status values must be given when using the IF and ELSEIF commands. The individual checks can be ANDed or ORed together, as well as be negated via NOT operations. The collective TRUE test result controls whether the associated block of instructions gets executed.

An implicit test result for an ELSE command controls whether its associated instruction block gets executed.

MI IF constructs can be nested to many levels with each requiring an ENDIF command to get closed.

In interactive mode only, the ALL option is available for the ENDIF command to achieve immediate closure of all active MI IF levels.

Syntax

where:

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.
Valid comparison operators:
Condition Definition
= equal to
<> , != , ~= , or ^= not equal to
> greater than
>= greater than or equal to
< less than
<= less than or equal to
When multiple checks are used, AND, OR and NOT keywords must be used to describe the evaluation action. Each check and action result must have surrounding parenthesis. A mix of actions can't be used within the same pair of parentheses.
instruction
An SQL request or one or more BTEQ commands.

Usage Notes

To use an MI IF rather than an SI IF, the THEN clause is omitted on the IF command. BTEQ will then start tracking that a new MI IF level has been opened and start parsing the IF condition to see if it has a TRUE result. If so, then its associated block of instructions gets executed. If the condition is FALSE, BTEQ will instead bypass that block and search for an ELSEIF, ELSE or ENDIF command. If it finds an ELSEIF that has a TRUE condition result, then it will execute that block – otherwise bypass it. If it does not find a TRUE condition for an ELSEIF and encounters an ELSE, the block of instructions for the ELSE is assumed to be the TRUE block. When BTEQ encounters an ENDIF, it will close out the associated MI IF level meaning bypassing of otherwise FALSE blocks will stop.

Once BTEQ finds a TRUE block, it will not attempt to resolve the conditions associated with any following ELSEIF command in the MI IF level and will instead bypass those blocks as well as any ELSE block in the level.

BTEQ will be strict about certain syntax errors found during batch mode. For example, if it encounters ELSEIF, ELSE or ENDIF commands at a point where no MI IF levels have yet been opened, it will generate a fatal error.

The maximum number of nested MI IFs is dependent on available storage with a hard limit of 4294967294 levels.

An MI IF can not be issued using an SQL ECHO statement.

An SI IF can be issued using an SQL ECHO statement as long as its THEN clause is specifying a valid BTEQ command that is also valid to be issued using an SQL ECHO statement.

GOTO commands can also be used to effect branching within a script. They provide the ability to jump forward in the script to a specific LABEL command and can be successfully used to mimic MI IF constructs. (BTEQ versions older than 16.20.00.06 only allowed the SI IF syntax and GOTOs had to be used.) But actual MI IF constructs should be given preference instead for the following reasons:
  • Intuitively Designed Conditional Constructs
  • Nested Conditional Paths
  • Easier to Understand Scripts
  • Fewer Scripted Instructions for Multi-Block Constructs
  • Efficient Condition Result Use for Single-Instruction Blocks
  • Custom Label Name Conventions only needed for GOTO Jumps
  • Having the Option for Less Verbose Output

GOTOs can be used in combination with MI IFs. They can to jump into, between and out of any block within any nested level of an MI IF construct. When a target label is found within an MI IF construct, its associated block is then considered the TRUE block for that level – as are its parent blocks from parent levels – from then on.

When a GOTO is active, instructions gets skipped until such time that the target LABEL command is found and executed. Informational messages get produced to reflect which instructions have been skipped. Similar diagnostic messages are produced for MI IF blocks that are not having their instructions executed. Those messages indicate the instructions are being bypassed.

The BRANCHMSG command can be used to specify the output format for branching messages is to be terse rather than verbose. See the BRANCHMSG command section for further information. It is strongly recommended to not turn off verbose mode until you are absolutely sure your script works as expected.

All LABEL commands encountered during GOTO skipping must be examined. But when a GOTO is not active, LABEL commands are subject to being bypassed when they exist in an MI IF.

A single instruction can be comprised of one SQL request or a sequence of one or more BTEQ commands that are continued over one or more input lines using intervening semicolons and trailing dashes. The choice of how many BTEQ commands to issue in a single instruction is left up to the user. However, some BTEQ commands require their own dedicated instruction to work properly so the choice is not always available to make.

An MI IF construct can not be used within any part of the THEN clause for an IF SI as this may lead to unexpected script results. BTEQ does take steps to detect this but can not detect every invalid syntax case. So it is strongly advised that users take steps to ensure their script is written correctly with regard to use of all MI IFs before putting it into production use.

There is a known parsing scenario which will give a misleading error when an MI IF is used in a THEN clause and that MI IF is missing its leading period. For example:

.if activitycount=0 then if errorcode=99;

Rather than giving an error first to reject the nested MI IF, BTEQ will generate an error about the missing '.' period. Once the period gets added BTEQ will return the fatal error message as follows:

*** Error: Nesting a multi-instruction (MI) IF under a single-instruction
           (SI) IF..THEN is not allowed. If an MI IF was not intended,
           review and correct the syntax as needed to ensure successful
           THEN keyword parsing.
           Terminating the application.

BTEQ parses only one command at a time for immediate execution, skipping or bypassing. This means there could be some invalid MI IF construct command sequence scenarios that BTEQ can only reactively detect after the fact – if at all – rather than proactively upfront. For example, when it encounters an IF command, it does not look ahead in the script to see if the required ENDIF is missing. And BTEQ has to employ a minimized parsing strategy for MI IF, ELSEIF, ELSE and ENDIF commands encountered during block bypassing or GOTO skipping. It must forego looking at the arguments given with these commands and is therefore forced to be lenient and overlook any syntax errors. It will only be the case that BTEQ does it normal full syntax validation command by command when the commands are getting actually executed as part of a "true" block. It is therefore left up to users to take responsibility for making sure all the paths of the MI IF construct get vetted before production use.

To detect the opening of an MI IF construct, BTEQ must be able to determine whether the given IF command includes a THEN clause. Some invalid syntax cases will obscure detection of the THEN keyword which will force BTEQ to incorrectly decide to start a new MI IF level. This syntax violation will lead to unexpected command execution paths. BTEQ may not detect any anomaly at all until it hits the end of the script. Under the forced assumption, it may just determine that an ENDIF is missing. It is again the user's responsibility to thoroughly vet their conditional logic before putting it into production.

BTEQ is SQL transaction unaware and does not have means of triggering transaction roll-backs based on command syntax errors. So it is very important that the scripted use of multi-instruction IF constructs be thoroughly tested for correctness before being put into production.

Example 1 – Framework Commands for a Multi-level MI IF Construct

.IF ACTIVITYCOUNT = 1
    <instructions>
    .IF ERRORCODE != 0
        <instructions>
    .ELSEIF ( ( INSERTCOUNT = 1 ) AND ( UPDATECOUNT = 2 ) )
        <instructions>
    .ELSE
        <instructions>
    .ENDIF
.ELSEIF ACTIVITYCOUNT = 2
    <instructions>
.ELSE
    <instructions>
.ENDIF

Example 2 – Branch on Database Error using an SI IF

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 3 – Quit on No Rows Found using an SI IF

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 4 – SI IF Instigated as an SQL ECHO Statement

When used in a Teradata SQL macro, the IF...THEN... command has the following format:

ECHO '.IF ERRORCODE = 3000 THEN .REMARK ''Next'' ';

Example 5 – 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
XSPs (Externally-stored Procedures) and UDFs (User-Defined Functions) differ from SSPs in that whenever they are changed in any way, a recompile to their associated library is required. This means the DROP activity associated with an XSP has compiler results while the DROP activity for an SSP does not.

Example 6 – 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

Example 7 – Producing Conditional Messages

One common use of BTEQ testing and branching is to produce a message if certain conditions are met. For example, a script might include a request that is expected to change the database. In this case, an activity count of zero indicates a problem, even if no error code is returned. If the request is critical to the operations, try including the following command in the script, right after the request:

.IF ACTIVITYCOUNT = 0 THEN .QUIT 65

In this case, the return code of 65 would have been predefined to indicate that the request did not change the database.

Or, before quitting BTEQ, try branching to a different part of the script and use the REMARK command to specify a descriptive message to elaborate on the return code, as follows:

.IF ACTIVITYCOUNT = 0 THEN .GOTO PROB65
   .
   .
   .
.LOGOFF
.EXIT 0
.LABEL PROB65
.REMARK 'Prob 65: The . . . request did not affect any rows'
.QUIT 65