Error check after each SQL statement

[Apna]

I have a shell 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

- Difference by tableSEL *  FROM  table1
MINUS
SEL  *  FROM  table2;

SEL *  FROM  table2
MINUS
SEL  *  FROM  table1;

-- Difference by final countSEL COUNT(*)  FROM  table1
MINUS
SEL  COUNT(*)  FROM  table2;

-- Difference by snap datesSEL date1, COUNT(*)  FROM  table1 GROUP BY 1
MINUS
SEL date2, COUNT(*)  FROM  table2 GROUP BY 1;

Thanks.

[Johannes_Vink]

Not sure how to handle in an shell script, but BTEQ has something:

/* Verify that the DBQL Temporary Tables are empty */
Select 'Rows Exist-Stop'
From SYS_MGMT.DBQLOGTBL_TMP SAMPLE 1;
.if activitycount <> 0 THEN .GOTO LD_Error1

.label LD_Error1
.remark 'LD_Error1: One or more of the DBQL Temp tables are not empty'
.quit 50

[Apna]

Hi Johannes ,

Thanks for your reply. I did try using this logic , but the script exit after first select query. Here is the code I used:

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

.SET maxerror 1

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

.IF ACTIVITYCOUNT <> 0 THEN .GOTO errors

sel count(*) from table1

minus

sel count(*) from table2;

.quit 0

.label errors

.IF ACTIVITYCOUNT > 0 THEN .GOTO Continue

sel count(*) from table1

minus

sel count(*) from table2;

ELSE

.quit

.logoff

.quit

EOF

[dnoeth]

Your script quits after the 1st successful Select.

You need to apply the logic after each Select: 

.SET maxerror 1
 
/**** Here I have script to create tables, apply other requirements ***/
 
sel count(*) from table1
minus
sel count(*) from table2;
.IF ACTIVITYCOUNT <> 0 THEN .GOTO errors

sel count(*) from table1
minus
sel count(*) from table2;

.IF ACTIVITYCOUNT <> 0 THEN .GOTO errors

.quit 0
 
.label errors
.quit 50

EOF