Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.4k views
in Technique[技术] by (71.8m points)

check if volatile table exists in teradata

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

1.4m articles

1.4m replys

5 comments

56.8k users

...