Teradata中MultiCaseMatch报错：期望在'multicasematch'与'('间有';'，求排查

错误原因 #

Teradata原生SQL中没有内置MultiCaseMatch函数，数据库无法识别这个语法元素，因此抛出expected something like ';' between the word 'multicasematch' and '('的语法错误。你可能混淆了其他数据平台的语法或自定义函数的使用方式。

替代实现方案 #

要实现你需求的“多条件匹配并返回对应分类”逻辑，可以用Teradata原生SQL实现，以下是两种常见方式：

方式1：展开所有匹配的分类（每行一个匹配结果） #

CREATE SET TABLE AGE_CASES (
    ID INTEGER,
    NAME VARCHAR(10),
    AGE INTEGER
) PRIMARY INDEX(ID);
 
-- 插入数据
INSERT INTO AGE_CASES VALUES (1, 'John',2);
INSERT INTO AGE_CASES VALUES (2, 'Freddy',6);
INSERT INTO AGE_CASES VALUES (3, 'Marie', 17);
INSERT INTO AGE_CASES VALUES (4, 'Tom', 22);
INSERT INTO AGE_CASES VALUES (5, 'Becky', 25);

WITH age_categories AS (
    SELECT 'infant' AS category, 0 AS min_age, 1 AS max_age UNION ALL
    SELECT 'toddler' AS category, 1 AS min_age, 2 AS max_age UNION ALL
    SELECT 'kid' AS category, 2 AS min_age, 12 AS max_age UNION ALL
    SELECT 'teenager' AS category, 13 AS min_age, 19 AS max_age UNION ALL
    SELECT 'youngAdult' AS category, 16 AS min_age, 25 AS max_age UNION ALL
    SELECT 'Adult' AS category, 21 AS min_age, 40 AS max_age
)
SELECT 
    ac.ID,
    ac.NAME,
    ac.AGE,
    acat.category
FROM AGE_CASES ac
JOIN age_categories acat
    ON ac.AGE BETWEEN acat.min_age AND acat.max_age
ORDER BY ac.ID;

方式2：将所有匹配分类聚合到一行 #

如果需要把同一行数据匹配的所有分类放在一个字段中，可以使用ARRAY_AGG函数：

WITH age_categories AS (
    SELECT 'infant' AS category, 0 AS min_age, 1 AS max_age UNION ALL
    SELECT 'toddler' AS category, 1 AS min_age, 2 AS max_age UNION ALL
    SELECT 'kid' AS category, 2 AS min_age, 12 AS max_age UNION ALL
    SELECT 'teenager' AS category, 13 AS min_age, 19 AS max_age UNION ALL
    SELECT 'youngAdult' AS category, 16 AS min_age, 25 AS max_age UNION ALL
    SELECT 'Adult' AS category, 21 AS min_age, 40 AS max_age
)
SELECT 
    ac.ID,
    ac.NAME,
    ac.AGE,
    ARRAY_AGG(acat.category) AS matched_categories
FROM AGE_CASES ac
JOIN age_categories acat
    ON ac.AGE BETWEEN acat.min_age AND acat.max_age
GROUP BY ac.ID, ac.NAME, ac.AGE
ORDER BY ac.ID;

内容的提问来源于stack exchange，提问作者bluemoon