CREATE MACRO FAILED ')' and ')'

Teradata Debugger
Enthusiast

CREATE MACRO FAILED ')' and ')'

DATABASE DB2;

CREATE MACRO CL_LINE_DENIAL ( CL_PAID_DATE_ID BIGINT) AS (
SELECT
SV.CL_ID,
SV.V_LINE,
mem.MED_ID,
mem.FIRST_NAME,
mem.LAST_NAME,
CASE WHEN prov.INST_NAME IS NULL THEN TRIM(prov.PRAC_FIRST_NAME)||' '||TRIM(prov.PRAC_LAST_NAME) ELSE prov.INST_NAME END AS PROV_NAME,
A.NPI AS PROV_NPI,
A.IRS_ID AS PROV_TIN,
A.IRS_NAME AS PAY_TO_NAME,

A.CLA_HAT_CODE,
A.CLA_HAT_DESC,
A.PARTICIPATING_PROV_IND,
A.MEDICAID_ID AS PROV_MEDICAID_ID,
SV.PRIMARY_DIAG_CODE,
SV.PROC_CODE_1,
dpm1.PROC_MODIFIER_CODE AS PROC_MOD_CODE_1,
dpm1.PROC_MODIFIER_DESC AS PROC_MOD_DESC_1,
SV.PROC_CODE_2,
pos.PLACE_OF_SERV_CODE AS POS_CODE,
SV.SERVICE_START_DATE_ID,
SV.SERVICE_END_DATE_ID,

RCVDATE.DATE_DATE as CL_RCVD_DATE,
SV.CLEAN_DATE_ID AS CL_CLEAN_DATE,
SV.BILLED_AMT,
SV.ALWD_PROV_AMT,
CASE
when ASR.RPTING_GROUP_CODE = 'INFO' then 'PAY'
when SV.CL_PAID_DATE_ID = -2 then 'PEND'
else ASR.RPTING_GROUP_CODE
end AS REPORTING_GROUP_CODE,
CASE
when (ASR.RPTING_GROUP_CODE = 'PEND' OR SV.CL_PAID_DATE_ID = -2) then 0
when ASR.RPTING_GROUP_CODE = 'DENY' then 0
else SV.PAID_AMT
end as PAID_AMT,
SV.INTEREST_AMT,
SV.CL_PAID_DATE_ID,
ASR.ADJDCTN_STATUS_REASON_CODE AS ADJDCTN_CLS_STATUS_REASON_CODE,
ASR.ADJDCTN_STATUS_REASON_DESC AS ADJDCTN_STATUS_REASON_DESC,
ads.ADJDCTN_STATUS_CODE AS ADJDCTN_STATUS_CODE,
ads.ADJDCTN_STATUS_DESC AS ADJDCTN_STATUS_DESC,
asr1.ADJDCTN_STATUS_REASON_CODE AS CLS_STATUS_REASON_CODE_1,
asr1.ADJDCTN_STATUS_REASON_DESC AS STATUS_REASON_DESC_CODE_1_DESC1

FROM DB1.FT_SERV_TRANS SV

INNER JOIN DB1.FT_CL_TRANS CL
ON SV.CL_ID = CL.CL_ID
AND SV.PLAN_ID = CL.PLAN_ID


JOIN DB1.DIM_MEM_CURR mem
ON SV.MEM_CURR_ID = mem.MEM_CURR_ID
AND mem.PLAN_ID =1021256

LEFT JOIN DB1.DIM_ADJDCTN_STATUS_REASON ASR
ON SV.ADJDCTN_STATUS_REASON1_ID = ASR.ADJDCTN_STATUS_REASON_ID

LEFT JOIN DB1.DIM_AILIATION_CURR A
ON A.AILIATION_CURR_ID = SV.ATTENDING_PROV_CURR_A_ID

LEFT JOIN DB1.DIM_PROVIDER_CURR prov
ON SV.ATTENDING_PROV_CURR_ID = prov.PROV_CURR_ID

JOIN DB1.DIM_ADJDCTN_STATUS ads
ON SV.ADJDCTN_STATUS_CODE_ID = ads.ADJDCTN_STATUS_CODE_ID

LEFT JOIN DB1.DIM_ADJDCTN_STATUS_REASON asr1
ON SV.ADJDCTN_STATUS_REASON1_ID = asr1.ADJDCTN_STATUS_REASON_ID

LEFT JOIN DB1.DIM_ADJDCTN_STATUS_REASON asr2
ON SV.ADJDCTN_STATUS_REASON2_ID = asr2.ADJDCTN_STATUS_REASON_ID

LEFT JOIN DB1.DIM_ADJDCTN_STATUS_REASON asr3
ON SV.ADJDCTN_STATUS_REASON3_ID = asr3.ADJDCTN_STATUS_REASON_ID

LEFT JOIN DB1.DIM_ADJDCTN_STATUS_REASON asr4
ON SV.ADJDCTN_STATUS_REASON4_ID = asr4.ADJDCTN_STATUS_REASON_ID

JOIN DB1.DIM_PLACE_OF_SERVICE pos --table with place of service info
ON SV.PLACE_OF_SERV_ID = pos.PLACE_OF_SERV_ID

LEFT JOIN DB1.DIM_PROCEDURE_MODIFIER dpm1
ON SV.PROC_MODIFIER1_ID = dpm1.PROC_MODIFIER_ID

LEFT JOIN DB1.DIM_PROCEDURE_MODIFIER dpm2
ON SV.PROC_MODIFIER2_ID = dpm2.PROC_MODIFIER_ID

LEFT JOIN DB1.DIM_DATE as RCVDATE
ON CL.CL_rcvd_date_ID = RCVDATE.DATE_ID

WHERE SV.CL_PAID_DATE_ID = :CL_PAID_DATE_ID
AND SV.PLAN_ID = 1021256
AND SV.MAX_SERV_SEQ_IND = 'Y'
AND ASR.RPTING_GROUP_CODE IN ('DENY')
AND asr1.ADJDCTN_STATUS_REASON_CODE NOT IN ('X09145','X0B7453','X77X14100I', 'X144858')

QUALIFY max(SV.V_LINE) over (Partition by SV.CL_ID) = count(ASR.RPTING_GROUP_CODE) over (Partition by SV.CL_ID)


ORDER BY SV.PAID_AMT

);

 

WHen I try to create Macro with this statement I get an error saying: 'CREATE MACRO Failed. [3706] Syntax error: expected something between ')' and ')'

  • macro
  • Stored Procedure
  • syntax error

Accepted Solutions
Junior Contributor

Re: CREATE MACRO FAILED ')' and ')'

There's the final semicolon missing:

ORDER BY SV.PAID_AMT;
);

Btw, when you use SQL Assistant, better switch to .NET, which shows the position where the parser failed:
x.jpg

 

 

 

1 ACCEPTED SOLUTION
2 REPLIES
Junior Contributor

Re: CREATE MACRO FAILED ')' and ')'

There's the final semicolon missing:

ORDER BY SV.PAID_AMT;
);

Btw, when you use SQL Assistant, better switch to .NET, which shows the position where the parser failed:
x.jpg

 

 

 

Enthusiast

Re: CREATE MACRO FAILED ')' and ')'

Thank you!