The Dialogue Manager status return variable, &RETCODE, indicates the status of FOCUS query commands. You can use it to test RDBMS return codes. The &RETCODE variable contains the last return code resulting from an executed report request, MODIFY request, or native SQL command (issued with or without Direct SQL Passthru). See SQL Codes and Adapter Messages for a list of common SQL return codes.

In a Dialogue Manager request, you can use a -IF command to test the &RETCODE value against a specified SQL or DBC return code. You can then take corrective actions based on the result of the -IF test. An SQL return code of zero (0) indicates a successful execution, a positive return code indicates a warning, and a negative return code indicates an error. For Teradata, any non-zero return code is either a warning or an error.

The following FOCEXEC issues the FOCUS CREATE FILE command to create the DB2 EMPINFO table. The FOCEXEC tests for an SQL return code of -601, generated when a DB2 table already exists. The -TYPE command displays message text. The first -TYPE command displays the &RETCODE value. The second explains that the table exists.

CREATE FILE EMPINFO 
-RUN 
-TYPE RETCODE IS  &RETCODE 
-IF &RETCODE EQ -601 GOTO DUPL; 
-EXIT 
-DUPL 
-TYPE  THIS TABLE ALREADY EXISTS. SPECIFY ANOTHER TABLE NAME. 
-EXIT  

When you execute the FOCEXEC, the following messages display:

 >  (FOC1400) SQLCODE IS  : -601/FFFFFDA7
 (FOC1421) TABLE EXISTS ALREADY. DROP IT OR USE ANOTHER TABLENAME
 (FOC1414) EXECUTE IMMEDIATE ERROR.
 RETCODE IS     -601
 THIS TABLE ALREADY EXISTS. SPECIFY ANOTHER TABLE NAME
 >

Since the table already exists, the RDBMS generates the SQL return code -601, and the &RETCODE variable stores the code. The expression in the -IF command is true, and the text messages display.

Note: Another useful Dialogue Manager variable, &FOCERRNUM, stores the last FOCUS or adapter (not RDBMS) error number generated by the execution of a FOCEXEC. See your FOCUS documentation for information about &FOCERRNUM and other statistical variables.