带有值列表的 Teradata 变量 [英] Teradata variable with list of values

问题描述

有没有办法在变量中传递值列表并在 IN() 语句中使用它来根据传入的值列表检查字段?

Is there a way to pass a list of values in a variable and use that in an IN() statement to check a field against the passed in list of values?

我唯一能想到的就是这样的:

The only thing I can think of is something like this:

SELECT
  field_name
WHERE
  (SELECT INSTR(@variable_list, field_name))>0

谢谢.

推荐答案

TD14 支持一个不错的表函数，名为 STRTOK_SPLIT_TO_TABLE:

TD14 supports a nice table function called STRTOK_SPLIT_TO_TABLE:

REPLACE MACRO testmac(param VARCHAR(1000)) AS
(
SELECT * FROM dbc.DatabasesV AS db
JOIN 
 (
   SELECT token AS DatabaseName, tokennum
   FROM TABLE (STRTOK_SPLIT_TO_TABLE(1, :param, ',')
        RETURNS (outkey INTEGER, -- usually the PK of the table, here it's just a dummy
                 tokennum INTEGER, -- order of the token within the param string
                 token VARCHAR(128) CHARACTER SET UNICODE)
              ) AS d 
 ) AS dt
ON db.DatabaseName  = dt.DatabaseName
ORDER BY tokennum;
);

EXEC testmac('dbc,systemfe,syslib');

这篇关于带有值列表的 Teradata 变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！