Books
in black and white
Main menu
Share a book About us Home
Books
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics
Ads

Teradata RDBMS forUNIX SQL Reference - NCR

NCR Teradata RDBMS forUNIX SQL Reference - NCR, 1997. - 913 p.
Download (direct link): teradataforunix1997.pdf
Previous << 1 .. 102 103 104 105 106 107 < 108 > 109 110 111 112 113 114 .. 241 >> Next


ABORT is flagged non-ANSI when the SQL flagger is enabled.

Syntax ABORT1-----------n-----------------n------------------,-------------------H

'—quotestring^ ^-FROM option^WHERE clause-^

FF07A068

where:

Syntax Element ... Is . . .
quotestring the text of the message to be returned when the transaction is terminated. If quotestring is omitted, the message defaults to: user-generated transaction ABORT
FROM option an optional clause that may be required if the WHERE clause includes subqueries. The contents of the FROM option are described in Chapter 7, “Queries: The SELECT Statement,” under the “FROM List”. ABORT is non-ANSI.
WHERE clause an optional clause that introduces a conditional expression. The expression may specify an aggregate operation (see Usage Notes). If the WHERE clause is omitted, termination is unconditional. See also Chapter 7, “Queries: The SELECT Statement,” the “WHERE Clause”. The WHERE condition specifies an expression whose result must be true if termination is to occur. If the result is false, transaction processing continues.

ABORT tests each value separately; therefore, the WHERE clause ABORT With a WHERE should not introduce both an aggregate and a non-aggregate value.

Clause The aggregate value becomes, in effect, a GROUP BY value, and the

mathematical computation is performed on the group aggregate results.

8-4

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

ABORT

For example, assuming that

• The table Test contains several rows,

• The sum of Test.colA is 188, and

• Only one row contains the value 125 in Test.colB

then, the following statement incorrectly terminates the transaction:

ABORT WHERE (SUM(Test.colA) <> 188)

AND (Test.ColB = 125);

The preceding statement is processed first by performing an all-rows scan with the condition (ColB = 125), which selects a single row and then computes intermediate aggregate results with the condition (SUM(ColA) <> 188).

The condition tests true because the value of ColA in the selected row is less than 188.

If ABORT ... WHERE is used and it requires READ access to an object for execution, the user executing this DML statement must have SELECT right to the data being accessed.

The WHERE condition of an ABORT may include subqueries. If so, then the subqueries require FROM clauses, and the ABORT should have a FROM if it is desired that the scope of a reference in a subquery is the ABORT condition. See also “Correlated Subqueries” in Chapter 9.

Refer also to the “ROLLBACK” statement.

If a macro or multistatement request contains multiple ABORT Muldple aBORt statements, those ABORT statements are processed in order, even if

Statements the expressions could be evaluated immediately.

There are two categories of ABORT statements, those that: can be Two Types °f aBOrts evaluated by the parser and do not require access to a table and

those that require table access.

If ABORT expressions are used that do not reference tables, and if their order of execution is not important relative to the other statements, then they should be placed ahead of any statements that reference tables so that the abort can be done at minimum cost.

In the following example, the first two ABORT statements can be evaluated by the parser, and do not require access to tables. The third ABORT requires access to a table.

REP MACRO macroname (P1 INTEGER, P2 INTEGER)

AS. . .

ABORT 'error' WHERE :P1 < 0;

ABORT 'error' WHERE :P2 < 0;

SELECT. . .

ABORT 'error' WHERE tab.C1 = :P1;

Teradata RDBMS for UNIX SQL Reference

8-5
Teradata SQL Syntax Guide

ABORT

Examples

Example 1

Example 2: Valid ABORT in All Releases

Example 3: Valid in V2R2.0 and Above But Not in Prior Releases

Example 4

The following examples illustrate the use of ABORT:

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.

CM DelEmp (num SMALLINT FORMAT '9(5)', dname VARCHAR(12), dept SMALLINT FORMAT '999')

AS ( ABORT 'Name does not exist' WHERE :num NOT IN

(SELECT EmpNo FROM Employee WHEREUPPER (Name) = UPPER

(:dname))

; DELETE FROM Employee WHERE UPPER (Name) = UPPER (:dname)

; UPDATE Department SET EmpCount = EmpCount - 1 WHERE DeptNo = :dept; ) ;

The following queries are syntactically correct in previous releases and the current release:

ABORT WHERE user='DBC';

The following queries are illegal in all previous releases but legal in the current and future releases:

ABORT from t1 WHERE t1.x1=1;

ABORT FROM t1 WHERE t1.x1 > 1;

ABORT FROM t1,t2 WHERE t1.x1=t2.x2;

The following example uses the ABORT statement in macro NewEmp. NewEmp adds a row to 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 (300).
Previous << 1 .. 102 103 104 105 106 107 < 108 > 109 110 111 112 113 114 .. 241 >> Next