我正在努力避免错误:
ERROR: Teradata execute: Object 'MY_TABLE' does not exist.
从SAS 执行TERADATA SQL时
这是我使用的原始SAS代码:
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;
根据文档,.SET ERRORLEVEL 3807 SEVERITY 0
应该可以解决我的问题。
我尝试在DROP TABLE
语句之前插入以下内容:
execute(.SET ERRORLEVEL 3807 SEVERITY 0) by TERADATA;
execute(ECHO '.SET ERRORLEVEL 3807 SEVERITY 0') by TERADATA;
我试着把两者结合起来:
execute(ECHO '.SET ERRORLEVEL 3807 SEVERITY 0'; Drop table MY_TABLE;) TERADATA;
在没有ECHO
的调用中出现语法错误,或者在尝试ECHO
变体时对错误没有影响。
问题是.SET ERRORLEVEL
不是SQL语句,而是BTEQ命令。根据文档,应该可以从标准TERADATA执行BTEQ命令。SQL应该可以使用ECHO
构造。但从SAS来看,这似乎并不奏效。
我只需要一个解决方案来避免SAS错误,SAS侧的解决方案以及TERADATA解决方案对我来说都可以。
为什么不询问Teradata表是否存在,然后让SAS有条件地运行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;
我不知道从SAS创建SP的语法,但我怀疑你是否有权这样做。
您可能会要求DBA安装以下SP,它会删除任何类型的表(包括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;
如果您将其作为proc调用运行,这可能会对您有所帮助:
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;
从sas通过:调用
execute (call drop_if_exists(MY_TABLE);) by TERADATA;
编辑:SAS调用过程创建
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;