quickneasycat quickneasycat - 1 month ago 7
SQL Question

SQL query with syntax error

I can't figure out why I'm getting this error.

I keep getting the Incorrect syntax near the keyword 'FROM' error.

USE [DWH]
GO

IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP TABLE #temp
create table #temp ([month] date, amt NUMERIC(36,2))

declare @date date = '1/1/16'

while @date <= '8/1/2017' BEGIN

IF OBJECT_ID('tempdb..#Members') IS NOT NULL
DROP TABLE #Members

select distinct MT1.MEMBER_ID
into #members
from DWH.[Dimensions].[D_MEMBER_T1] MT1
join DWH.[Dimensions].[D_MEMBER_T2] MT2 ON MT1.MEMBER_T1_SKEY = MT2.MEMBER_T1_SKEY
JOIN DWH.[Dimensions].[D_PROVIDER_T1] PT1 ON MT2.[PCP_PROVIDER_T1_SKEY] = PT1.PROVIDER_T1_SKEY

WHERE @date BETWEEN MT2.EFFECTIVE_START_DATE AND MT2.EFFECTIVE_END_DATE
AND MT2.[MEM_ELIGIBILITY_STATUS_FLAG] = 'Y'
AND PT1.TAX_ID IN ( '610654587',
'205494939',
'205497203',
'610444707',
'610461940')

insert into #temp

SELECT @DATE, (CAST(ROUND(SUM([PAID_AMT])*1.00,2) AS NUMERIC (36,2))

FROM DWH.[Dimensions].[D_CLAIM_LINE_PHARMACY_DETAIL_T1]

WHERE DATEPART (YYYY,FILL_DATE) = DATEPART (YYYY,@DATE)
AND DATEPART (MM,FILL_DATE) = DATEPART (MM,@DATE)

SET @DATE = DATEADD (MM,1,@DATE)
END

SELECT * FROM #TEMP
GO

Answer Source

This code has unbalanced parentheses:

SELECT @DATE, (CAST(ROUND(SUM([PAID_AMT])*1.00,2) AS NUMERIC (36,2))
--------------^
FROM DWH.[Dimensions].[D_CLAIM_LINE_PHARMACY_DETAIL_T1]

It should be:

SELECT @DATE, CAST(ROUND(SUM([PAID_AMT])*1.00, 2) AS NUMERIC (36, 2))
FROM DWH.[Dimensions].[D_CLAIM_LINE_PHARMACY_DETAIL_T1]

The FROM is in the parentheses, which is generating the error.