Terminators | SQL Fundamentals | Teradata Vantage - Terminators - Advanced SQL Engine - Teradata Database

SQL Fundamentals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
zwv1557098532464.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1141
lifecycle
previous
Product Category
Teradata Vantage™

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 ; ) ;