Clarrification required in bteq (.SET MAXERROR)

Tools
Kde
Enthusiast

Clarrification required in bteq (.SET MAXERROR)

Hi All,

If we have multiple insert statement in bteq where insert2 depends on insert1, in this scenario if insert1 fails i want to come out of script instead of proceeding to insert2.

This can be achived if we use .IF ERRORCODE <> 0 after each insert statement , but happened to see .SET MAXERROR settings in BTEQ document which can be used to abort the script even if any one of the statement errorlevel is greater than specified value.

bteq script
/*************************************************************/
.SET MAXERROR 0;

.logon localtd/tduser,tduser;

sel date;
se date;
sel date;

.logoff;
.quit;

/*************************************************************/

The above bteq script is not working... eventhough i specified to abort the script when errorlevel greater than 0

log for reference

+---------+---------+---------+---------+---------+---------+---------+----
.SET MAXERROR 0;
+---------+---------+---------+---------+---------+---------+---------+----

.logon localtd/tduser,

*** Logon successfully completed.
*** Teradata Database Release is 12.00.03.18
*** Teradata Database Version is 12.00.03.18
*** Transaction Semantics are BTET.
*** Character Set Name is 'ASCII'.

*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----

sel date;

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

Date
--------
11/03/12

+---------+---------+---------+---------+---------+---------+---------+----

se date;

se date;
$
*** Failure 3706 Syntax error: expected something between the beginning of
the request and the word 'se'.
Statement# 2, Info =5
*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----

sel date;

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

Date
--------
11/03/12

+---------+---------+---------+---------+---------+---------+---------+----

.logoff;
*** You are now logged off from the DBC.
+---------+---------+---------+---------+---------+---------+---------+----
.quit;
*** Exiting BTEQ...
*** RC (return code) = 8

/********************************************************************/

Same script when i specify MAXERROR 1 then it is working fine.

BTEQ 13.00.00.02 Sat Mar 12 00:47:46 2011

+---------+---------+---------+---------+---------+---------+---------+----
.SET MAXERROR 1;
+---------+---------+---------+---------+---------+---------+---------+----

.logon localtd/tduser,

*** Logon successfully completed.
*** Teradata Database Release is 12.00.03.18
*** Teradata Database Version is 12.00.03.18
*** Transaction Semantics are BTET.
*** Character Set Name is 'ASCII'.

*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----

sel date;

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

Date
--------
11/03/12

+---------+---------+---------+---------+---------+---------+---------+----

se date;

se date;
$
*** Failure 3706 Syntax error: expected something between the beginning of
the request and the word 'se'.
Statement# 2, Info =5

*** Exiting BTEQ...
*** RC (return code) = 8

/**********************************************************************/

question 1: Just want to understand why MAXERROR level 0 is not working ...
question 2: If there is any other way to abort the script apart from using IF ERRORCODE and MAXERROR , please let me know.

Thanks in advance
Sakthi

2 REPLIES
Senior Apprentice

Re: Clarrification required in bteq (.SET MAXERROR)

..SET MAXERROR 0 is probably used to switch off automatic termination.

But as the minimum errorlevel is 4 setting it to 1,2 or 3 works as expected.

I usually do:
.set errorlevel unknown severity 99
.set maxerror 1

Dieter
Kde
Enthusiast

Re: Clarrification required in bteq (.SET MAXERROR)

Thanks a lot Dieter