BTEQ exits with Wrong Error code

Database

BTEQ exits with Wrong Error code

 

Hi,

My bteq script has an update statement. semicolon is missing at the end of the update statement and the update fails. but the bteq is returning error code '0'. so am unable to catch the error when my process runs. i have a label defined 'SQLERROR' and am using . IF ERRORCODE<>0 then .GOTO SQLERROR. so when the update fails, it should go to SQLERROR label and exit with .quit 50, but thats not happening. It exits with ERRORCODE=0. below is my code. can anyone help me with it.

BTEQ Script:

========

bteq << EOF

.set session transaction btet;

.IF ERRORCODE <> 0 THEN .GOTO SQLERROR;

.logon XXXXX ;

.IF ERRORCODE <> 0 THEN .GOTO SQLERROR;

.set errorout stdout;

.IF ERRORCODE <> 0 THEN .GOTO SQLERROR;

.set width 254;

.IF ERRORCODE <> 0 THEN .GOTO SQLERROR;

.SET ERRORLEVEL 3807 SEVERITY 0;

Drop table example.table1

.SET ERRORLEVEL 3807 SEVERITY 8;

CREATE TABLE example.table1

AS(SELECT * FROM example.table ) WITH DATA;

.IF ERRORCODE <> 0 THEN .GOTO SQLERROR;

UPdate a from example.table1 a, example.table2 b

set temp_fl=1

where a.GCI =b.GCI

.IF ERRORCODE <> 0 THEN .GOTO SQLERROR;

.quit 0

.LABEL SQLERROR

.quit 50

EOF

LOG File:

======

BTEQ 13.10.00.06 Mon Dec 29 13:26:34 2014

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

.set session transaction btet;

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

.IF ERRORCODE <> 0 THEN .GOTO SQLERROR;

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

.logon XXXX/XXXX,

*** Logon successfully completed.

*** Teradata Database Release is 13.10.07.35

*** Teradata Database Version is 13.10.07.21

*** Transaction Semantics are BTET.

*** Session Character Set Name is 'ASCII'.

*** Total elapsed time was 1 second.

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

.IF ERRORCODE <> 0 THEN .GOTO SQLERROR;

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

.set errorout stdout;

*** Error messages now directed to STDOUT.

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

.IF ERRORCODE <> 0 THEN .GOTO SQLERROR;

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

.set width 254;

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

.IF ERRORCODE <> 0 THEN .GOTO SQLERROR;

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

.SET ERRORLEVEL 3807 SEVERITY 0;

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

Drop table example.table1;

*** Table has been dropped.

*** Total elapsed time was 8 seconds.

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

.SET ERRORLEVEL 3807 SEVERITY 8;

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

CREATE TABLE example.table1

AS

(SELECT * FROM example.table ) WITH DATA;

*** Table has been created.

*** Total elapsed time was 5 seconds.

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

.IF ERRORCODE <> 0 THEN .GOTO SQLERROR;

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

UPdate a from example.table1 a, example.table2 b

set temp_fl=1

where a.GCI =b.GCI

.IF ERRORCODE <> 0 THEN .GOTO SQLERROR;

.IF ERRORCODE <> 0 THEN .GOTO SQLERROR;

$

*** Failure 3706 Syntax error: expected something between a string or a Unicode character literal and '.'.

Statement# 2, Info =204

*** Total elapsed time was 1 second.

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

.quit 0

*** You are now logged off from the DBC.

*** Exiting BTEQ...

*** RC (return code) = 0




7 REPLIES
N/A

Re: BTEQ exits with Wrong Error code

Sunny,

It's because the last statement that ran was successful.

The syntax error caused the update to not run.

RGlass

Re: BTEQ exits with Wrong Error code

what do you mean by last statement. ? Update statement is the last statement right. If the update is not running succesfully, it should go to SQLERROR label and exit with return code 50 right. correct me if am wrong.

Re: BTEQ exits with Wrong Error code

and in my actual script , there is only 1 update statement. in this sample sript which i attached i have a create table statement and an update statement. If y]you are saying that 'create table' statement is the last statement that ran succesfully, then in my original script there is no create table statement. It just has an update statement with a missing colon and that error is not being caught.

N/A

Re: BTEQ exits with Wrong Error code

Every SQL statement is termintated by a semicolon and this is missing before the 'IF', so the '.IF' is part of the UPDATE statement (I already wrote that in the 2nd copy of your question).

This is the actual statement sent to Teradata and it's obviously no valid syntax:

UPdate a from example.table1 a, example.table2 b
set temp_fl=1
where a.GCI =b.GCI
.IF ERRORCODE <> 0 THEN .GOTO SQLERROR;

There's no '.IF' after this.

Re: BTEQ exits with Wrong Error code

i understood this..but how do we catch such errors when a semicolon in missing in a script. ? is there a solution for it

N/A

Re: BTEQ exits with Wrong Error code

The only way is testing the SQL code before in BTEQ :-)

Re: BTEQ exits with Wrong Error code

lol..am surprised that there isnt a way to catch hold of such small errors in bteq