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.
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
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
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