英文:
SAS Enterprise Guide Drop Volatile Tables If Any Exist
问题
I'm attempting to create a SAS macro that can drop any/all volatile tables in a current Teradata session.
当前，唯一成功运行的方法是在宏的开头创建一个虚拟的临时表，该表允许使用Teradata中的HELP VOLATILE TABLE选项创建一个包含所有表名的表。
我目前遇到的问题是，如果我删除虚拟表的创建（XXXX），并且我的会话中没有任何临时表，那么HELP VOLATILE TABLE将不返回任何内容，并导致无法创建表。
当不存在临时表时的警告/错误。
警告：DROP、KEEP或RENAME列表中的变量'Table Dictionary Name'n从未被引用过。
错误：PROC SQL要求任何创建的表至少有1列。
是否有一种方法可以避免创建虚拟表的需要（如下所示）？
英文:
I'm attempting to create a SAS macro that can drop any/all volatile tables in a current Teradata session.
Currently the only way to have it run successfully is to create a dummy volatile table at the beginning of the macro that allows the creation of a table containing all table names found using the HELP VOLATILE TABLE option in Teradata.
The current issue I'm running into is if I remove the dummy table creation (XXXX) and I don't have any volatile tables in my session then HELP VOLATILE TABLE doesn't return anything and leads to the inability to create a table.
Warning/Errors when no volatile tables are present.
WARNING: The variable 'Table Dictionary Name'n in the DROP, KEEP, or RENAME list has never been referenced.
ERROR: PROC SQL requires any created table to have at least 1 column.
Is there a way around having the need to create a dummy table (like shown below)?
%macro dropvts(DropALL=N,USE_COMMIT_WORK=N)/parmbuff;%put syspbuff contains: &syspbuff;RESET NOPRINT;EXECUTE(CREATE VOLATILE TABLE XXXX, LOG (XXXX CHAR(4))PRIMARY INDEX ( XXXX )ON COMMIT PRESERVE ROWS) BY TERADATA;%IF %UPCASE(&USE_COMMIT_WORK)=Y %THEN %DO;EXECUTE(COMMIT WORK) BY TERADATA;%END;CREATE TABLE VTs (RENAME=('Table Dictionary Name'n=TableName) KEEP='Table Dictionary Name'n) ASSELECT * FROM CONNECTION TO TERADATA (HELP VOLATILE TABLE); /*** CHG PROJ ID 409 PLF ***/%IF %UPCASE(&DropALL)=N %THEN %DO;%local parm;%do parm=1 %to %sysfunc(countw(%sysfunc(compress(&syspbuff))));%let exists=0;SELECT 1 INTO :exists FROM VTsWHERE UPCASE(TableName)="%UPCASE(%SCAN(&syspbuff,&parm))";%IF &exists=1 %THEN %DO;EXECUTE(DROP TABLE %SCAN(%sysfunc(compress(&syspbuff)),&parm)) BY TERADATA;%IF %UPCASE(&USE_COMMIT_WORK)=Y %THEN %DO;EXECUTE(COMMIT WORK) BY TERADATA;%END;%PUT VT %SCAN(&syspbuff,&parm) has been dropped;%END;%ELSE %DO;%PUT VT %SCAN(&syspbuff,&parm) does not exist;%END;%end;CREATE TABLE VTs (RENAME=('Table Dictionary Name'n=TableName) KEEP='Table Dictionary Name'n) ASSELECT * FROM CONNECTION TO TERADATA (HELP VOLATILE TABLE);EXECUTE(DROP TABLE XXXX) BY TERADATA;%IF %UPCASE(&USE_COMMIT_WORK)=Y %THEN %DO;EXECUTE(COMMIT WORK) BY TERADATA;%END;%END;%ELSE %DO;SELECT TableNameinto :voltable1-:voltable999FROM VTs;%local table;%do table=1 %to &sqlobs;EXECUTE(DROP TABLE &&voltable&table) BY TERADATA;%IF %UPCASE(&USE_COMMIT_WORK)=Y %THEN %DO;EXECUTE(COMMIT WORK) BY TERADATA;%END;%end;%END;RESET PRINT;%mend;
Any help is greatly appreciated.
答案1
得分: 2
在这个宏 %TDEXIST() 中（用于测试特定名称的表、视图或临时表是否存在以及它的类型），我首先运行一个单独的查询来检查是否存在任何临时表。
所以，如果您已经连接到 TERADATA 并且想要测试宏变量 TABLE 中指定的表是否存在，您可以使用以下代码：
*----------------------------------------------------------------------------;* 检查是否存在任何临时表 ;*----------------------------------------------------------------------------;%let exists=0;select '1' into :exists from connection to TERADATA (help volatile table);%if (&exists) %then %do;*----------------------------------------------------------------------------;* 检查这个临时表是否存在 ;*----------------------------------------------------------------------------;select 'VOLATILE TABLE' into :resultfrom connection to TERADATA (help volatile table)%*----------------------------------------------------------------------------Set VARNAME based on VALIDVARNAME setting.-----------------------------------------------------------------------------;%if %sysfunc(getoption(validvarname))=ANY %thenwhere upcase('table name'n) = "&table";%elsewhere upcase(table_name) = "&table";;%let exists=&sqlobs;%end;
英文:
What I did in this macro %TDEXIST() (which tests if a table, view or volatile table with a specific name exists and what type it is) is run a separate query first to check if there are any volatile tables or not.
So if you have a connection to TERADATA already existing and you want to test if a table with the name in the macro variable TABLE exists you could use this code.
*----------------------------------------------------------------------------;* Check for any Volatile tables ;*----------------------------------------------------------------------------;%let exists=0;select '1' into :exists from connection to TERADATA (help volatile table) ;%if (&exists) %then %do;*----------------------------------------------------------------------------;* Check if this Volatile table exists ;*----------------------------------------------------------------------------;select 'VOLATILE TABLE' into :resultfrom connection to TERADATA (help volatile table)%*----------------------------------------------------------------------------Set VARNAME based on VALIDVARNAME setting.-----------------------------------------------------------------------------;%if %sysfunc(getoption(validvarname))=ANY %thenwhere upcase('table name'n) = "&table";%elsewhere upcase(table_name) = "&table";;%let exists=&sqlobs;%end;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论