ABORT Examples | SQL Statements | Teradata Vantage - Examples - Advanced SQL Engine - Teradata Database

SQL Data Manipulation Language

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

Example: ABORT Statement

In the following example, the ABORT statement terminates macro execution if the row for the employee being deleted is not present in the employee table.

Statements in the body of the macro are entered as one multistatement request. Therefore, if the WHERE condition of the ABORT statement is met, the entire request is aborted and the accuracy of the count in the department table is preserved.

     CREATE MACRO del_emp (num SMALLINT FORMAT '9(5)',
        dname VARCHAR(12),
        dept  SMALLINT FORMAT '999') AS (
     ABORT 'Name does not exist' WHERE :num NOT IN (
     SELECT emp_no
     FROM employee
     WHERE UPPER(name) = UPPER(:dname))
    ;DELETE FROM employee
     WHERE UPPER(name) = UPPER(:dname)
    ;UPDATE department
      SET emp_count = emp_count - 1
      WHERE dept_no = :dept; ) ;

Example: Valid ABORT Statements

The following ABORT statements are all syntactically correct.

     ABORT
     WHERE user= 'DBC';

     ABORT
     FROM table_1
     WHERE table_1.x1 = 1;

     ABORT
     FROM table_11
     WHERE table_1.x1 > 1;

     ABORT
     FROM table_1,table_2
     WHERE table_1.x1 = table_2.x2;

Example: ABORT Statement With WHERE Clause

The following example uses an ABORT statement in the macro newemp. newemp inserts a row into the employee table for each new employee and then executes the SELECT statement to verify that the new information was entered correctly. The ABORT statement ensures that no new employee is inadvertently added to the executive office department, department 300.

     CREATE MACRO  newemp  (
        number   INTEGER,
        name     VARCHAR(12),
        dept     INTEGER 100 TO 900,
        position VARCHAR(12),
        sex      CHARACTER,
        ed_lev   BYTEINT ) AS (
     ABORT 'Department number 300 not valid'
     WHERE :dept = 300 ;
     INSERT INTO employee (emp_no, name, dept_no, job_title, sex,
                           ed_lev)
     VALUES (:number, :name, :dept, :position, :sex, :edlev) ;
     SELECT *
     FROM employee
     WHERE emp_no = :number ; ) ;

Example: ABORT With a UDT In The WHERE Clause

The following examples show correct use of UDT expressions in the WHERE clause of an ABORT statement:

     ABORT WHERE (tab1.euro_col < CAST(0.0 AS euro));

     ABORT WHERE (tab1.cir_col.area() < 10.0);

Example: Using an SQL UDF in the WHERE Clause of an ABORT Statement

The following ABORT statement specifies an SQL UDF in its WHERE search condition.

     ABORT FROM t1
     WHERE a1 = test.value_expression(2, 3);