Teradata: expected something like ';' between the word 'multicasematch' and '('

31 views Asked by At

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

There are 0 answers