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