Executing Macro error 3707 - expected something like a name or a unicode delimited identifier

Database
Visitor

Executing Macro error 3707 - expected something like a name or a unicode delimited identifier

Hello,

I am having trouble executing a macro on a query that I am sure works.  I've read the other posts here but haven't found the answer yet. 

When I execute the marco I get the error: "[3707] syntax error, expected something like a name or a unicode delimited identifier between '(' and ')'."

Here is the code:

CREATE MACRO DASH_AGG_INSERT(V_WK_NM VARCHAR(10), V_ST_DT DATE, V_EN_DT DATE) AS (
INSERT INTO B.TEMP_DASH_AGG_DATA
WITH CTE AS (SELECT TH.*,
        MAX(TIER) OVER (PARTITION BY CUSTOMER_ID ORDER BY TIER_ACTIVITY_RANK ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) LAG_TIER
FROM S.TIER_HISTORY TH
WHERE 1 = 1)
SELECT :V_WK_NM,
            :V_ST_DT,
            :V_EN_DT,
            Z.TIER,
            Z.LAG_TIER,
            Z.TIER_ENTERED_PROCESS,
            CASE WHEN LAG_OG_END_DATE BETWEEN :V_ST_DT AND :V_EN_DT THEN 1 ELSE 0 END AS EXPIRE_FLAG,
             CASE WHEN T.TXNS_12M > 0 THEN 1 ELSE 0 END AS ACTIVE_12M,
            CASE WHEN T.TXNS_L30D > 0 THEN 1 ELSE 0 END AS ACTIVE_L30D,
             COUNT(*) MEMBERS,
             SUM(TXNS_12M) AS TXNS_12M,
             SUM(SALES_12M) AS SALES_12M,
             SUM(QTY_12M) AS QTY_12M
FROM (SELECT Y.*,
                  CASE WHEN TIER IN ('Pro','Elite Pro') AND ADD_MONTHS(MIN_CONSEC_PRO,12*COUNT_CONSEC_PRO) >            TIER_EXPIRATION_DATE
                THEN ADD_MONTHS(MIN_CONSEC_PRO,12*COUNT_CONSEC_PRO) ELSE TIER_EXPIRATION_DATE END AS          OG_END_DATE,
                 MAX (CASE WHEN TIER IN ('Pro','Elite Pro') AND ADD_MONTHS(MIN_CONSEC_PRO,12*COUNT_CONSEC_PRO) >                 TIER_EXPIRATION_DATE
                THEN ADD_MONTHS(MIN_CONSEC_PRO,12*COUNT_CONSEC_PRO) ELSE TIER_EXPIRATION_DATE END)
                 OVER (PARTITION BY CUSTOMER_ID ORDER BY TIER_EVENT_START_DATE ROWS BETWEEN UNBOUNDED  PRECEDING AND 1 PRECEDING) LAG_OG_END_DATE
               FROM (
                      SELECT X.*,
                         MIN(TIER_EVENT_START_DATE) OVER (PARTITION BY CUSTOMER_ID, GRP) MIN_CONSEC_PRO,
                         COUNT(TIER_EVENT_START_DATE) OVER (PARTITION BY CUSTOMER_ID, GRP ORDER BY                 TIER_EVENT_START_DATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) COUNT_CONSEC_PRO
                       FROM (
                           SELECT A.*,
                         (SELECT COUNT(*)
                          FROM CTE B
                          WHERE A.CUSTOMER_ID = B.CUSTOMER_ID
                           AND B.TIER_ACTIVITY_RANK <= A.TIER_ACTIVITY_RANK
                             AND COALESCE(B.TIER,'A') <> COALESCE(B.LAG_TIER,'A')
                            ) AS GRP
                       FROM CTE A) X) Y) Z
                       LEFT JOIN (SELECT CUSTOMER_ID,
                                           COUNT(*) TXNS_12M,
                                            SUM(SALES_GAAP_AMT) SALES_12M,
                                             SUM(SALES_GAAP_QTY) QTY_12M,
                                             COUNT(CASE WHEN STM.BUSINESS_DATE >= :V_EN_DT-30 THEN 1 ELSE NULL END) TXNS_L30D,
                                          SUM(CASE WHEN STM.BUSINESS_DATE >= :V_EN_DT-30 THEN SALES_GAAP_AMT ELSE 0 END)  SALES_L30D,
                                      SUM(CASE WHEN STM.BUSINESS_DATE >= :V_EN_DT-30 THEN SALES_GAAP_QTY ELSE 0 END) QTY_L30D
                                           FROM S.TRANS_MTRC STM
                                          WHERE STM.BUSINESS_DATE BETWEEN ADD_MONTHS(:V_EN_DT,-12)+1 AND :V_EN_DT
                                         GROUP BY 1) T
ON Z.CUSTOMER_ID = T.CUSTOMER_ID
WHERE :V_EN_DT BETWEEN Z.TIER_EVENT_START_DATE AND Z.TIER_EVENT_END_DATE
GROUP BY 1,2,3,4,5,6,7,8,9;);

 

EXECUTE DASH_AGG_INSERT(V_WK_NM='WK49', V_ST_DT= '2017-12-30', V_EN_DT = '2018-01-06' )

 

I'm able to create the macro, but get the error at exec. Any help would be appreciated.

Justin