0

using bteq script, I want to delete the temporary error and log tables created during TPT load namely ErrorTable1, ErrorTable2, LogTable.

I have prepared following bteq script with if-else, which will drop tables if it exists

.LOGON ${HOST}/${USER},${PASS};

SELECT 1 FROM dbc.TablesV WHERE CONCAT(DataBaseName, '.', TableName) = 'Test.ErrorTable1';
.IF ACTIVITYCOUNT = 0 THEN GOTO CHECK_1;
DROP TABLE Test.ErrorTable1;


.LABEL CHECK_1;
SELECT 1 FROM dbc.TablesV WHERE CONCAT(DataBaseName, '.', TableName) = 'Test.ErrorTable2';
.IF ACTIVITYCOUNT = 0 THEN GOTO CHECK_2;
DROP TABLE Test.ErrorTable2;


.LABEL CHECK_2;
SELECT 1 FROM dbc.TablesV WHERE CONCAT(DataBaseName, '.', TableName) = 'Test.LogTable';
.IF ACTIVITYCOUNT = 0 THEN GOTO DONE;
DROP TABLE Test.LogTable;


.LABEL DONE;

.LOGOFF
.EXIT

My question is, in teradata bteq, can I use ACTIVITYCOUNT multiple times, like in above script, to check various table's existence?

axnet
  • 5,146
  • 3
  • 25
  • 45
  • 1
    Of course, AVTIVITYCOUNT is reset with every SQL statement. But you might simplify your logic to `.IF ACTIVITYCOUNT = 0 THEN DROP TABLE Test.ErrorTable2;` or use a Storerd Procedure: https://stackoverflow.com/a/39771103/2527905 – dnoeth Nov 26 '20 at 07:43
  • Thanks fred and dnoeth – axnet Nov 26 '20 at 17:15
  • Correcting earlier incorrect comment: Though `CONCAT` function is now supported, the ANSI concatenation operator `||` is preferred. – Fred Nov 26 '20 at 17:38
  • Also note that (though not needed for this case) current versions of BTEQ support "multi-instruction" `.IF` `.ELSEIF` `.ELSE` `.ENDIF` blocks (which can be nested) in addition to `.IF ... THEN` single-instruction syntax. – Fred Nov 26 '20 at 17:42
  • Thanks Fred can you point me to some example snippet of IF, ELSEIF, ELSE etc. usage in bteq script, I do not really wish to do GOTO if full-blown if-else-if is supported by bteq in teradata – axnet Nov 27 '20 at 05:06

0 Answers0