Error check after each SQL statement in bteq script

Database
Highlighted
Fan

Error check after each SQL statement in bteq script

I have a shell/bteq script and it has multiple SELECT SQL statements. Each SELECT should be tested before executing the next statement. The issue is the SELECT statement either returns zero rows (or) rows, If zero rows then no error and if the SELECT statement returns rows then the script should stop and exit. I have errorcode logic at the end of the script, but I need something to check at each SQL statement.

 

SyntaxEditor Code Snippet

 

bteq .run file=/home/.tdlogon<< EOF>/ home//logs/script.log

 

.SET maxerror 1

.SET width 60000

.SET RECORDMODE OFF;

.SET FORMAT OFF ;

.SET TITLEDASHES OFF;

.SET SEPARATOR ',';

 

 

/**** Here I have script to create tables, apply other requirements ***/

 /*** Below is the check on difference between two tables created. If any of the select statement returns rows then the script should stop and exit (this is important as there will be a drop statement at the end of script which should be executed only if the select statements returns zero difference)   ***/

 

-- Difference by key column

sel col5,col8 from table1

minus

sel col6,col8 from table2;

 

 

-- Difference by count

sel count(*) from table1

minus

sel count(*) from table2;

 

 

 

.logoff

.quit

EOF

 

Thanks.

1 REPLY
Enthusiast

Re: Error check after each SQL statement in bteq script

Just use ACTIVITYCOUNT after each SELECT which will exit the bteq if there are any rows rerturned.

-- Difference by key column
sel col5,col8 from table1
minus
sel col6,col8 from table2;
 .IF ACTIVITYCOUNT<>0 THEN .EXIT 20
 
-- Difference by count
sel count(*) from table1
minus
sel count(*) from table2;
 .IF ACTIVITYCOUNT<>0 THEN .EXIT 20

You can use the return code 20 in the parent script to give a proper explanation in the logs/notifications.