16.20 - Status Examples - Basic Teradata Query

Basic Teradata Query Reference

Basic Teradata Query
October 2018
Programming Reference

Example 1 – Successful SELECT

Assume a Teradata SQL SELECT request succeeded and returned the contents of 23 rows. The ERRORCODE value associated with the request would be zero and the ACTIVITYCOUNT would be 23. The value of ERRORLEVEL would depend upon whether any prior submitted requests resulted in an error or not.

Example 2 – Successful DELETE

Assume a Teradata SQL DELETE request encountered no rows that met the specified delete criteria, but encountered no error conditions. The resulting ERRORCODE and ACTIVITYCOUNT values would both be zero. The value of ERRORLEVEL would depend upon whether any prior submitted requests resulted in an error or not.

Example 3 – Unsuccessful SELECT

Assume the database encountered an error condition while processing an SQL SELECT request. The resulting ACTIVITYCOUNT value would be 0. But the ERRORCODE would be the non-zero error code the database returns. If the severity value mapped to the ERRORCODE value is greater than the pre-existing ERRORLEVEL value, then ERRORLEVEL is raised to that severity value.

Example 4 – Successful REPLACE PROCEDURE with Compiler Messages

Assume the database was able to re-compile a Stored Procedure but the compiler returned warnings and perhaps an error. Also assume the ERRORONDBSWARNING setting is OFF. The resulting ACTIVITYCOUNT value would be equal to the number of compiler messages returned. Since the database was able to successfully compile, the ERRORCODE would be zero. But the WARNINGCODE value would be the non-zero warning code the database returns. For an SQL-stored procedure, a 5526 warning code would indicate the compiler gave an error whereas a 5527 would indicate it only gave warnings. Since ERRORCODE is zero, ERRORLEVEL is not subject to change.

If it were actually the case that the compiler itself could not be successfully used to attempt a compile, the database would have returned an error code rather than a warning code.

Example 5 – Making a system call to run another BTEQ process

The WARNINGCODE value can be specified on an EXIT command as the value to use as BTEQ's return code. The OS command can be used to invoke another BTEQ process. Assume that second process ends with an EXIT command that supplies its WARNINGCODE value. That value then becomes the "calling" BTEQ process's SYSTEMRETURNCODE value.

For more information about error codes and severity levels, refer to Error Handling. The tables in BTEQ Return Codes list the Teradata Database error messages that are assigned default severity levels (return codes) of 04, 08, and 012, respectively. For a complete list of all Teradata Database error codes, refer to Messages (B035-1096).