Drop Table If Exists Equivalent for Teradata from Sas
I'm Trying to Avoid the Error: Error: Teradata Execute: Object 'My_Table' Does Not Exist. When Executing Teradata Sql from Sas This Is the Original Sas Code...
I'm trying to avoid the error:
ERROR: Teradata execute: Object 'MY_TABLE' does not exist.
When executing TERADATA SQL from SAS
This is the original SAS code I'm using:
proc sql;
connect to TERADATA (user='my_user' password=XXXXXXXXXX MODE=TERADATA TDPID='bdpr');
execute(database MY_DB) by TERADATA;
execute(Drop table MY_TABLE;) by TERADATA;
disconnect from TERADATA;
quit;
According to the documentation the .SET ERRORLEVEL 3807 SEVERITY 0 should fix my problem.
I tried inserting the following before my DROP TABLE statement:
execute(.SET ERRORLEVEL 3807 SEVERITY 0) by TERADATA;
execute(ECHO '.SET ERRORLEVEL 3807 SEVERITY 0') by TERADATA;
I tried combining both:
execute(ECHO '.SET ERRORLEVEL 3807 SEVERITY 0'; Drop table MY_TABLE;) TERADATA;
With either a syntax error for the calls without ECHO or no effect on the error when trying the ECHO variants.
The problem is that the .SET ERRORLEVEL is not a SQL statement but a BTEQ command. According to the docs it should be possible to execute BTEQ commands from standard TERADATA SQL should be possible using the ECHO construct. But from SAS this doesn't seem to be working.
I only need a solution to avoid the SAS error, both SAS side solutions as well as TERADATA solutions are ok for me.
3 Answers
Why not ask Teradata if the table exists and then have SAS conditionally run the drop?
%let tablekind=NONE;
select obj into :tablekind trimmed from connection to teradata
(select case when (tablekind in ('T','O')) then 'TABLE'
else 'VIEW' end as obj
from dbc.tablesv
where databasename = 'MY_DB' and tablename= 'MY_TABLE'
and tablekind in ('V','T','O')
)
;
%if &tablekind ne NONE %then %do;
execute(drop &tablekind. MY_DB.MY_TABLE;) by teradata;
%end;
I don't know the syntax to create an SP from SAS, but I doubt you will have the right to do so.
You might ask your DBA to install following SP, which drops any kind of table (including Volatile).
--Drop a table without failing if the table doesn’t exist:
REPLACE PROCEDURE drop_table_if_exists
(
/* database name, uses default database when NULL.
Must be calling user for Volatile Tables
*/
IN db_name VARCHAR(128) CHARACTER SET Unicode,
/* table name */
IN tbl_name VARCHAR(128) CHARACTER SET Unicode,
OUT msg VARCHAR(400) CHARACTER SET Unicode
) SQL SECURITY INVOKER – check the rights of the calling user
BEGIN
DECLARE full_name VARCHAR(361) CHARACTER SET Unicode;
DECLARE sql_stmt VARCHAR(500) CHARACTER SET Unicode;
DECLARE exit HANDLER FOR SqlException
BEGIN
-- catch "table doesn't exist" error
IF SqlCode = 3807 THEN SET msg = full_name || ' doesn''t exist.';
ELSE
-- fail on any other error, e.g. missing access rights or wrong object tye
RESIGNAL;
END IF;
END;
SET full_name = '"' || Coalesce(db_name,DATABASE) || '"."' || Coalesce(tbl_name,'') || '"';
SET sql_stmt = 'DROP TABLE ' || full_name || ';';
EXECUTE IMMEDIATE sql_stmt;
SET msg = full_name || ' dropped.';
END;
Maybe this could help you, if you run this as a proc call:
replace procedure drop_if_exists( in_object varchar(50))
begin
IF EXISTS(SELECT 1 FROM dbc.tables WHERE tablename = in_object
and databasename='<your database name>') THEN
CALL DBC.SysExecSQL('DROP TABLE ' || in_object);
END IF;
END;
And call this from sas via:
execute (call drop_if_exists(MY_TABLE);) by TERADATA;
EDIT: SAS-invoked procedure creation
proc sql;
connect using DBCONN;
execute(
replace procedure drop_if_exists( in_object varchar(50))
begin
IF EXISTS(SELECT 1 FROM dbc.tables WHERE tablename = in_object
and databasename='<my database>') THEN
CALL DBC.SysExecSQL('DROP TABLE ' || in_object);
END IF;
END;
) by DBCONN;
disconnect from DBCONN;
quit;
Sarah Jenkins is a veteran tech journalist with over 12 years of experience covering artificial intelligence, mobile innovations, and digital ethics. Her insights have appeared in leading technology publications worldwide.
