1

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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.