如何在 Teradata SQL 宏中使用变量

发布于 2024-08-30 07:09:12 字数 1334 浏览 6 评论 0原文

我想在 Teradata 上的宏 SQL 中使用变量。

我以为我可以做如下的事情:

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

);  

但是,这不起作用,所以我想到了这个:

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'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?

Cheers
Tim

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

醉殇 2024-09-06 07:09:12

您不能在宏内部声明变量。如果您愿意,可以使用 UPDATE 语句来处理您想要完成的任务。

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
;

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
;
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文