Error Response (ANSI Session Mode Only) | SQL Fundamentals | Teradata Vantage - 17.10 - Error Response (ANSI Session Mode Only) - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - SQL Fundamentals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1141-171K
Language
English (United States)

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