How to Terminate Semicolon Recognition in SQL Requests | Basic Teradata Query - 17.00 - Terminating Semicolon Recognition - Basic Teradata Query

Basic Teradata® Query Reference

Basic Teradata Query
June 2020
Programming Reference

Although BTEQ passes SQL to the database without interpreting it, BTEQ must still perform a nominal amount of input parsing, sufficient to determine where an SQL request begins and ends. Absence of a leading period character is interpreted to mean the start of an SQL request. To detect the end of a request, BTEQ needs to find a true terminating semicolon. During that search, BTEQ will look at the provided input lines character by character, and ignore certain parts of the text based on whether the characters exist within delimiters that BTEQ recognizes solely for that purpose. Specifically, comments, parenthesis, quotes and pass thru text strings comprise the set of recognized delimiters, and have significance as explained in the parsing rules section below. BTEQ does not attempt to validate SQL syntax. Upon finding what it considers to be a request-terminating semicolon, it passes the request to SQL Engine unvalidated.

The onus is on the BTEQ user to supply syntactically correct requests. Otherwise, BTEQ may incorrectly interpret where a terminating semicolon exists in the input, and end up submitting a bogus request.
BTEQ uses the following parsing rules to determine which parts of the input text to ignore while searching for the end of the request:
  • When a comment start "/*" is found, everything following it is ignorable until a comment end character sequence "*/" is found.
  • When a single-quote or double-quote outside a comment is found, everything following it is ignorable until a matching same-type quote is found.
  • For all left-parenthesis found outside a comment or a quoted string, the search for a terminating semicolon will continue until a matching right-parenthesis which does not appear in a comment or a quoted string is found for each one.
  • When an @BEGIN_PASS_THRU string is found, everything following it is ignorable until an @END_PASS_THRU string is found.

The only exception made for the above rules is the case where BTEQ is being used in batch mode and is processing the last instruction – or what it thinks is the last instruction – of the input (either stdin or RUN file).

In that case, BTEQ will end up sending the SQL request as-is to the database whether even though it may not have found a terminating semicolon.