BTEQ: Conditional Execution of SQL Statements

Tools
Enthusiast

BTEQ: Conditional Execution of SQL Statements

I am writing a BTEQ script to run multiple queries as a batch. In my BETQ script, my first query would be the count of records on the table and the second query will be Primary Key validation followed by some RI validation and so on.
If the first query returns me a zero, meaning there are no records in the table; I do not want to execute rest of queries on that table. Please let me know how to write it in BTEQ script?

select count(*) from xyzDatabase.abcTable;
if count(*) of xyzDatabase.abcTable > 0
Then execute all PK, RI queries related to xyzDatabase.abcTable
else
quit

4 REPLIES
Teradata Employee

Re: BTEQ: Conditional Execution of SQL Statements

BTEQ does not permit you to reference the COUNT(*) value in your script. But you could use .IF ACTIVITYCOUNT test if you modify the query:
SELECT COUNT(*) FROM MyTable HAVING COUNT(*) > 0;
That way, you get no row returned (ACTIVITYCOUNT=0) if the count is zero, and a one row with the COUNT(*) value returned if the count is nonzero (so ACTIVITYCOUNT=1).
Enthusiast

Re: BTEQ: Conditional Execution of SQL Statements

Hi,
The above solution will work but please bear in mind the count(*) = 0 is also a row retun. So this will cause activitycount <> 0. Hence you have to write a select statement which will not retun any row if the table is empty. Simplest would be to use something like "SELECT 1 FROM ;" and then check its activitycount. But remember one more drwback of this procedure is that if your table is large, then there will be a large # of "1" printed in the logging file of the BTEQ. So please use an appropriate Select section.
This is just for your information.
Enthusiast

Re: BTEQ: Conditional Execution of SQL Statements

SELECT
COUNT(*)
FROM
DBC.Tables
WHERE
Databasename = 'DATABASE_NAME'
AND Tablename = 'TABLE_NAME'
HAVING
COUNT(*) > 0;

.IF ERRORCODE <> 0 THEN .QUIT 10;

.IF ACTIVITYCOUNT = 1 THEN .GOTO ACT_1;
.IF ACTIVITYCOUNT = 0 THEN .GOTO ACT_2;

.LABEL DROP_TAB;
action 1 sql;

.LABEL CT_TAB;
action 2 sql;

Enthusiast

Re: BTEQ: Conditional Execution of SQL Statements

That is the purpose of the having clause.
Having Count(*)>0 will suppress any rows where count(*)=0.