MACRO在SQL中创建表 [英] MACRO to create a table in SQL

问题描述

大家好，非常感谢您花时间阅读本文.

Hi everyone thanks so much for taking the time to read this.

我想在 Teradata 中创建一个宏，该宏将根据特定参数从另一个表创建一个表.

I'd like to create a macro in Teradata that will create a table from another table based on specific parameters.

我的原始表由三列组成:patient_id、diagnostic_code 和 Date_of_birth......

My original table consists of three columns patient_id, diagnosis_code and Date_of_birth ......

我想构建一个宏，允许我指定诊断代码，然后构建包含具有该诊断代码的所有患者的数据的表格.

I'd like to build a macro that would allow me to specify a diagnosis code and it would then build the table consisting of data of all patients with that diagnosis code.

我当前的代码是这样的

Create Macro All_pats (diag char) as (
create table pats as(
select *
from original_table 
where diag = :diagnosis_code;)
with data primary index (patid); 

我似乎无法让它发挥作用 - 有什么提示吗?

I cant seem to get this to work - any tips?

再次感谢

推荐答案

您的代码在错误的位置有一个分号并且缺少右括号:

Your code has a semicolon in a wrong place and a missing closing bracket:

Create Macro All_pats (diag char) as (
  create table pats as
   (
    select *
    from original_table 
    where diag = :diagnosis_code
   ) with data primary index (patid);
);

将多个值作为分隔列表传递更复杂(除非您在存储过程中使用动态 SQL):

Passing multiple values as a delimited list is more complicated (unless you use Dynamic SQL in a Stored Procedure):

REPLACE MACRO All_lpats (diagnosis_codes VARCHAR( 1000)) AS
(
  CREATE TABLE pats AS
   (
     SELECT *
     FROM original_table AS t
     JOIN TABLE (StrTok_Split_To_Table(1, :diagnosis_codes, ',')
                 RETURNS (outkey INTEGER,
                          tokennum INTEGER,
                          token VARCHAR(20) CHARACTER SET Unicode)
                ) AS dt
     ON t.diag = dt.token
   ) WITH DATA PRIMARY INDEX (patid);
);

EXEC All_lpats('111,112,113');

顾名思义，StrTok_Split_To_Table 将一个分隔的字符串拆分成一个表格.您可能需要调整生成的标记的分隔符和长度.

As the name implies StrTok_Split_To_Table splits a delimited string into a table. You might need to adust the delimiter and the length of the resulting token.

这篇关于MACRO在SQL中创建表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！