15.00 - Error Response (ANSI Session Mode Only) - Teradata Database

Teradata Database SQL Fundamentals

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1141-015K

Error Response (ANSI Session Mode Only)

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  

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  

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