16.20 - Error Response (ANSI Session Mode Only) - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantageâ„¢ SQL Fundamentals

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-02
dita:mapPath
zce1519094756513.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.ditaval

An error response occurs when a query anomaly is severe enough to prevent the correct processing of the request.

In ANSI session mode, an error for a request causes the request to rollback, and not the entire transaction.

Example: Returning an Error Message

The following command returns the error message immediately following.

.SET SESSION TRANS ANSI;
 *** Error: You must not be logged on .logoff to change the SQLFLAG
or TRANSACTION settings.

Example: Returning Error Messages in ANSI Mode

Assume that the session is running in ANSI session mode, and the following table is defined:

CREATE MULTISET TABLE inv, FALLBACK,
   NO BEFORE JOURNAL,
   NO AFTER JOURNAL
   (
   item INTEGER CHECK ((item >=10) AND (item <= 20) ))
PRIMARY INDEX (item);

You insert a value of 12 into the item column of the inv table.

This is valid because the defined integer check specifies that any integer between 10 and 20 (inclusive) is valid.

INSERT INTO inv (12);

The following results message returns.

*** Insert completed. One row added....

You insert a value of 9 into the item column of the inv table.

This is not valid because the defined integer check specifies that any integer with a value less than 10 is not valid.

INSERT INTO inv (9);

The following error response returns:

*** Error 5317 Check constraint violation: Check error in field
inv.item.

You commit the current transaction:

COMMIT;

The following results message returns:

*** COMMIT done. ...

You select all rows from the inv table:

SELECT * FROM inv;

The following results message returns:

*** Query completed. One row found. One column returned.
   item
-------
     12