All Forums Tools
trying_to_learn 14 posts Joined 08/06
13 Dec 2006
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.abcTableelse quit

Fred 1096 posts Joined 08/04
13 Dec 2006

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

05 Jan 2007

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.

jndrfq 5 posts Joined 08/06
10 Jan 2007

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;

--
jndrfq

jklee 40 posts Joined 07/06
10 Jan 2007

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

You must sign in to leave a comment.