check if volatile table exists in teradata

Question

Volatile table are great and somewhat comparable to temp tables in sql server (my background). Is there a way to check if a volatile table exists already? This code won't work when it is run for the first time:

DROP TABLE SomeVolatileTable;
CREATE VOLATILE TABLE SomeVolatileTable AS
(
    SELECT 
        TOP 10 *
    FROM  SomeSourceTable
) WITH DATA ON COMMIT PRESERVE ROWS;

In sql server you can check if a temporary table exists:

IF OBJECT_ID('tempdb..#SomeTempTable') IS NOT NULL DROP TABLE #SomeTempTable

Does something similar exist in Teradata?

dnoeth · Accepted Answer · 2017-09-15T21:29:28.673

6

There's no way to check if a specific Volatile Table exists besides HELP VOLATILE TABLE which returns all VT.

But you might create a Stored Procedure like the following:

/*
   Drop a table ignoring 3807 error (Table doesn't exist)
*/

REPLACE PROCEDURE drop_table_if_exists
(
  IN db_name VARCHAR(128) CHARACTER SET Unicode,
  IN tbl_name VARCHAR(128) CHARACTER SET Unicode,
  OUT msg VARCHAR(400) CHARACTER SET Unicode
) SQL SECURITY INVOKER
BEGIN
   DECLARE full_name VARCHAR(361)  CHARACTER SET Unicode;

   DECLARE sql_stmt VARCHAR(500)  CHARACTER SET Unicode;
   DECLARE exit HANDLER FOR SqlException
   BEGIN
      IF SqlCode = 3807 THEN SET msg = full_name || ' doesn''t exist.';
      ELSE
        RESIGNAL;
      END IF;
   END;

   SET full_name = '"' || Coalesce(db_name,DATABASE) || '"."' || tbl_name || '"';

   SET sql_stmt = 'DROP TABLE ' || full_name || ';';

   EXECUTE IMMEDIATE sql_stmt;

   SET msg = full_name || ' dropped.';
END;

It will only ignore the Table doesn't exist error, but still fail on invalid rights, etc.

If you call it with your own user as database it also works for Volatile Tables:

CALL drop_table_if_exists(USER,'SomeVolatileTable', msg);

edited Sep 15 '17 at 21:29

answered Sep 29 '16 at 13:02

dnoeth

59,503
4
39
56

2

@csetzkorn: Ask your DBA to create it, it's safe because it will only drop tables where the submitting user has a DROP TABLE right. – dnoeth Sep 29 '16 at 13:25
great solution, thx! However on my machine I had to replace the SqlState 'T3807' by '42000' (classvalue + subclassvalue of the given sqlstate). – MarkusN Sep 15 '17 at 15:27
@MarkusN: Oops, that's right, I pasted a wrong version. Fixed, thanks. – dnoeth Sep 15 '17 at 21:27
This, like my answer, has the side effect of rolling back any enclosing transaction if the table does not exist. – Carlos A. Ibarra Sep 23 '18 at 05:15
1

@CarlosA.Ibarra: Correct, but each DDL statement like Drop Table must be commited individually anyway. I can't think of any reason why one would need to run it as the last request in a transaction, *DML-DML-DML-Drop Table*. – dnoeth Sep 23 '18 at 09:19

Carlos A. Ibarra · Answer 2 · 2018-09-23T05:11:59.603

2

You can use this stored procedure to check if a volatile table exists:

REPLACE PROCEDURE test_db.VOLATILE_EXISTS(
  IN volatile_table_name varchar(30),
  OUT exists_ind integer -- 1 if it exists, else 0
)
SQL SECURITY INVOKER
BEGIN
  DECLARE ignored_condition varchar(100) default '';
  DECLARE table_does_not_exist CONDITION FOR SQLSTATE '42000';
  DECLARE sql_string varchar(100);

  DECLARE CONTINUE HANDLER FOR table_does_not_exist
     SET ignored_condition = 'table does not exist';

  SET exists_ind = 0;
  SET sql_string =  'select 1 from '||volatile_table_name||' where 1=0';
  BEGIN
    DECLARE c1 CURSOR FOR s1;
    PREPARE s1 FROM sql_string;
    OPEN c1;
  END;

  IF ignored_condition = '' THEN
    SET exists_ind = 1;
  END IF;
END;

Be aware that if the VT does not exist and you call this SP within a BEGIN TRANSACTION / END TRANSACTION, the "table does not exist exception", although handled, will cause your transaction to roll back as a side effect. I don't know of a way to prevent that.

edited Sep 23 '18 at 05:11

answered Sep 19 '18 at 06:41

Carlos A. Ibarra

6,002
1
28
38

The rollback will occur in a Teradata session only. If you run it in an ANSI session it will not cause a rollback because the *3807 ... does not exist* is an *error* and not a *failure*. – dnoeth Sep 23 '18 at 09:22
Good point, thanks. I couldn't find any docs on which responses are considered errors and which are failures other than the vague "A failure response is a severe error" in SQL Fundamentals. Is this documented anywhere? – Carlos A. Ibarra Sep 23 '18 at 14:02
I don't know, I've couldn't find a list of errors vs. failures. I actually never searched for it because I usually don 't have to code in ANSI mode and in TD mode there's only failures :-) – dnoeth Sep 23 '18 at 15:09

score 1 · Answer 3 · answered Oct 06 '16 at 11:46

1

Rather than dropping before you try and create them, drop them later in the script after you are done with them. This will always work, will allow you to rerun without issue and will free up the spool space used by the volatile table.

answered Oct 06 '16 at 11:46

Jim Baldwin

71
2

3

*always work*: Well, when the script is failing before the VT is dropped you still have to deal with an existing table. – dnoeth Oct 06 '16 at 13:43
Good point dnoeth. I would usually try to resolve the issue from that point and continue execution from the query that failed. – Jim Baldwin Oct 06 '16 at 22:15

check if volatile table exists in teradata

3 Answers3

Linked