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

Example: Returning Error Messages When Using a SELECT Statement

The following SELECT statement

SELECT * FROM Inventory:;

in BTEQ, returns the failure response message:

*** Failure 3706 Syntax error: expected something between the word
 'Inventory' and ':'.
                Statement# 1, Info =20
*** Total elapsed time was 1 second.

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 commit the current transaction:

COMMIT;

The following results message returns:

*** COMMIT done. ...

You insert a valid value of 15 into the item column of the inv table:

INSERT INTO inv (15);

The following results message returns.

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

You can use the ABORT statement to cause the system to roll back the transaction:

ABORT;

The following failure message returns:

*** Failure 3514 User-generated transaction ABORT.
                Statement# 1, Info =0

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