ABORT Examples | SQL Statements | Teradata Vantage - Examples - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
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),
        gender   CHARACTER,
        ed_lev   BYTEINT ) AS (
     ABORT 'Department number 300 not valid'
     WHERE :dept = 300 ;
     INSERT INTO employee (emp_no, name, dept_no, job_title, gender,
                           ed_lev)
     VALUES (:number, :name, :dept, :position, :gender, :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);