Unable to execute the Teradata stored procedure due to syntax errors

teradata

ERROR: CALL Failed. 3707: SP_PARTICIPANTS_BY_AGE:Syntax error, expected something like a name or a Unicode delimited identifier between '(' and ')'.

Below is the query used

REPLACE  PROCEDURE  SANDBOX.SP_PARTICIPANTS_BY_AGE ()

DYNAMIC RESULT SETS 1
SQL SECURITY INVOKER 

BEGIN

    DECLARE c  CURSOR WITH RETURN ONLY FOR
        WITH 
            AgeData as (
                SELECT
                usr.USER_ID
                ,usr.BIRTH_DATE
                ,floor(months_between(CURRENT_DATE , usr.BIRTH_DATE )/12) AS AgeCalc
                FROM DV1HDSV.SPIRE_USERS usr
            )

        SELECT 
            MA.AGE_CATEGORY
            ,COUNT (DISTINCT fit.USER_ID) AS PARTICIPANT_COUNT
            ,CASE AGE_CATEGORY
            WHEN 'Under 30' THEN 1
            ELSE ROW_NUMBER() OVER (ORDER BY AGE_CATEGORY)
            END AS DATAORDER                         
        FROM
            (
                SELECT
                USER_ID
                ,AgeCalc
                ,CASE
                WHEN AgeCalc <=30 THEN 'Under 30'
                WHEN AgeCalc BETWEEN 31 AND 40 THEN '31-40'
                WHEN AgeCalc BETWEEN 41 AND 50 THEN '41-50'
                WHEN AgeCalc BETWEEN 51 AND 60 THEN '51-60'
                WHEN AgeCalc > 60 THEN 'Above 60'
                END AS AGE_CATEGORY
                FROM AgeData
            ) MA
            LEFT JOIN DV1HDSV.SPIRE_FITNESS_RECORDS fit
            ON fit.USER_ID = MA.USER_ID
        WHERE AGE_CATEGORY IS NOT NULL
        GROUP BY MA.AGE_CATEGORY
        ORDER BY DATAORDER;

    OPEN c;

END;

Best Answer

Teradata doesn't support CTE in stored procedures (as of 15.00), see SQL Data Manipulation Language > The SELECT Statement > WITH and WITH RECURSIVE Statement Modifiers. Surprisingly, in some cases WITH works, but in more complex cases you get a syntax error like that.

Related Question