16.10 - Terminators - Teradata Database

Teradata Database SQL Fundamentals

Product
Teradata Database
Release Number
16.10
Release Date
June 2017
Content Type
Programming Reference
Publication ID
B035-1141-161K
Language
English (United States)

The SEMICOLON is a Teradata SQL request terminator when it is the last nonblank character on an input line in BTEQ unless that line has a comment beginning with two dashes. In this case, the SEMICOLON request terminator must be on the line following the comment line.

A request is considered complete when either the “End of Text” character or the request terminator character is detected.

ANSI Compliance

The SEMICOLON as a request terminator is a Teradata extension to the ANSI/ISO SQL:2011 standard.

Example: Request Termination

On the following input line:

   SELECT *
   FROM Employee ;

the SEMICOLON terminates the single-statement request “SELECT * FROM Employee”.

BTEQ uses SEMICOLONs to terminate multistatement requests.

A request terminator is mandatory for request types that are:

  • In the body of a macro
  • Triggered action statements in a trigger definition
  • Entered using the BTEQ interface
  • Entered using other interfaces that require BTEQ

Example: Using a Request Terminator in the Body of a Macro

The following statement illustrates the use of a request terminator in the body of a macro.

   CREATE MACRO Test_Pay (number (INTEGER),
                          name (VARCHAR(12)),
                          dept (INTEGER) AS
   ( INSERT INTO Payroll_Test (EmpNo, Name, DeptNo)
     VALUES (:number, :name, :dept) ;
     UPDATE DeptCount
     SET EmpCount = EmpCount + 1 ;
     SELECT *
     FROM DeptCount ; )

Example: BTEQ Request

When entered through BTEQ, the entire CREATE MACRO statement must be terminated.

   CREATE MACRO Test_Pay
   (number (INTEGER),
    name   (VARCHAR(12)),
    dept   (INTEGER) AS
   (INSERT INTO Payroll_Test (EmpNo, Name, DeptNo)
    VALUES (:number, :name, :dept) ;
    UPDATE DeptCount
    SET EmpCount = EmpCount + 1 ;
    SELECT *
    FROM DeptCount ; ) ;