如何在Teradata SQL宏中使用变量 [英] How to use variables in Teradata SQL Macros
本文介绍了如何在Teradata SQL宏中使用变量的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想在Teradata的宏SQL中使用变量.
I'm wanting to use variables inside my macro SQL on Teradata.
我认为我可以做以下事情:
I thought I could do something like the following:
REPLACE MACRO DbName.MyMacro
(
MacroNm VARCHAR(50)
)
AS
(
/* Variable to store last time the macro was run */
DECLARE V_LAST_RUN_DATE TIMESTAMP;
/* Get last run date and store in V_LAST_RUN_DATE */
SELECT LastDate
INTO V_LAST_RUN_DATE
FROM DbName.RunLog
WHERE MacroNm = :MacroNm;
/* Update the last run date to now and save the old date in history */
EXECUTE MACRO DbName.RunLogUpdater(
:MacroNm
,V_LAST_RUN_DATE
,CURRENT_TIMESTAMP
);
);
但是,那没有用,所以我想到了:
However, that didn't work, so I thought of this instead:
REPLACE MACRO DbName.MyMacro
(
MacroNm VARCHAR(50)
)
AS
(
/* Variable to store last time the macro was run */
CREATE VOLATILE TABLE MacroVars AS
(
SELECT LastDate AS V_LAST_RUN_DATE
FROM DbName.RunLog
WHERE MacroNm = :MacroNm;
)
WITH DATA ON COMMIT PRESERVE ROWS;
/* Update the last run date to now and save the old date in history */
EXECUTE MACRO DbName.RunLogUpdater(
:MacroNm
,SELECT V_LAST_RUN_DATE FROM MacroVars
,CURRENT_TIMESTAMP
);
);
我可以使用存储过程来完成所需的工作,但是我想避免这种情况.
I can do what I'm looking for with a Stored Procedure, however I want to avoid for performance.
您对此有任何想法吗?
还有什么我可以尝试的吗?
Do you have any ideas about this?
Is there anything else I can try?
干杯
蒂姆
推荐答案
您不能在宏内部声明变量.如果您愿意,可以使用UPDATE语句来处理您要完成的任务.
You can't DECLARE a variable inside of a macro. What you are trying to accomplish could be handled with an UPDATE statement if you so choose.
UPDATE TGT
FROM <dbname>.<target table> TGT
, (SELECT MacroName
, LastRunDate
FROM <dname>.<source table>
) SRC
SET LastRunDate = SRC.LastRunDate
, EffectiveTimestamp = CURRENT_TIMESTAMP(0)
WHERE TGT.MacroName = SRC.MacroName
;
这篇关于如何在Teradata SQL宏中使用变量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文