I am trying out a MultiCaseMatch statement in Teradata where I get the error message "expected something like ';' between the word 'multicasematch' and '(' ". Would anyone be able to help me know what I'm doing wrong here.
This is my code.
CREATE SET TABLE AGE_CASES (
ID INTEGER,
NAME VARCHAR(10),
AGE INTEGER
)PRIMARY INDEX(ID);
-- insert values
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);
SELECT * FROM MultiCaseMatch(
ON(
SELECT t.*
,(case WHEN t.age <= 1 THEN '1' ELSE '0' END ) AS case1
,(CASE WHEN t.age>=1 AND t.age <=2 THEN '1' ELSE '0' END) AS case2
,(CASE WHEN t.age>=2 AND t.age<=12 THEN '1' ELSE '0' END) AS case3
,(CASE WHEN t.age>=13 AND t.age<=19 THEN '1' ELSE '0' END) AS case4
,(CASE WHEN t.age>=16 AND t.age<=25 THEN '1' ELSE '0' END) AS case5
,(CASE WHEN t.age>=21 AND t.age<=40 THEN '1' ELSE '0' END) AS case6
FROM AGE_CASES AS t
)
USING LABELS(
'case1 AS "infant"'
,'case2 AS "toddler"'
,'case3 AS "kid"'
,'case4 AS "teenager"'
,'case5 AS "youngAdult"'
,'case6 AS "Adult"'
)
)as dt;