March 5, 2018 at 12:34 pm
This code results in
Msg 102, Level 15, State 1, Line 29
Incorrect syntax near ','. That is the between the CTEs. I copied syntax from another report that i wrote that works. Thoughts?
;With CTE_1 AS (
SELECT
H.CUSTOMS_ENTRY_NUM as [Entry Num]
,L.Entry_Line_ID as Line
,H.[Broker_Invoice]
,H.Entry_Type
,L.PART_NUM AS [ESL Part]
,L.HTS_NUM AS [ESL HTS]
,CAST(L.ENTRY_DATE AS DATE) AS [Entry_Date]
,C.[CLASS_VALUE_1] as [P/C_HTS]
,C.[CLASSIFIED_DATE] as [P/C Class Date]
FROM
ADHOC.ATS_ESH H
INNER JOIN ADHOC.ATS_ESL L
ON H.TRANS_SK = L.TRANS_SK
/* Joining on Prod_Class */
LEFT JOIN [TSC].[EMS_TVC_PROD_CLASS] C
ON L.[PART_NUM] = Replace(C.[PROD_ID],'-','') AND C.[CTRY_CODE] = 'US' AND C.CLASS_TYPE = 'HS' and C.BUS_UNIT = 'FORD' AND C.[CLASS_DETAIL_LEVEL] = 'Import_Export'
WHERE (H.Importer = 'FORD'
AND H.Entry_Summary_Date > GetDate () - 30
AND L.HTS_Num NOT LIKE '98%'
AND L.Part_Num <> 'NPN'
AND H.Com_Desc_Code = 'Parts'
AND Substring(L.Part_Num,4,1) <> 'Z'
AND L.HTS_NUM <> ISNULL(C.[CLASS_VALUE_1], '')
)
, CTE_2 AS (
SELECT
Z.[COMPOSITE_PART]
,Z.HTS_NUMBER
,MAX(Z.CREATED_DATE) AS MAX_DATE
FROM [TSI].[ZATS_BROKER_FEED] Z
INNER JOIN ADHOC.ATS_ESL L
ON L.PART_NUM = Z.COMPOSITE_PART
AND Z.CREATED_DATE <= L.ENTRY_DATE
WHERE
Z.SUB_ORG='FORD'
GROUP BY
Z.COMPOSITE_PART
,Z.HTS_NUMBER
)
March 5, 2018 at 12:40 pm
jeffshelix - Monday, March 5, 2018 12:34 PMThis code results in
Msg 102, Level 15, State 1, Line 29
Incorrect syntax near ','. That is the between the CTEs. I copied syntax from another report that i wrote that works. Thoughts?
;With CTE_1 AS (
SELECT
H.CUSTOMS_ENTRY_NUM as [Entry Num]
,L.Entry_Line_ID as Line
,H.[Broker_Invoice]
,H.Entry_Type
,L.PART_NUM AS [ESL Part]
,L.HTS_NUM AS [ESL HTS]
,CAST(L.ENTRY_DATE AS DATE) AS [Entry_Date]
,C.[CLASS_VALUE_1] as [P/C_HTS]
,C.[CLASSIFIED_DATE] as [P/C Class Date]
FROM
ADHOC.ATS_ESH H
INNER JOIN ADHOC.ATS_ESL L
ON H.TRANS_SK = L.TRANS_SK
/* Joining on Prod_Class */
LEFT JOIN [TSC].[EMS_TVC_PROD_CLASS] C
ON L.[PART_NUM] = Replace(C.[PROD_ID],'-','') AND C.[CTRY_CODE] = 'US' AND C.CLASS_TYPE = 'HS' and C.BUS_UNIT = 'FORD' AND C.[CLASS_DETAIL_LEVEL] = 'Import_Export'
WHERE (H.Importer = 'FORD'
AND H.Entry_Summary_Date > GetDate () - 30
AND L.HTS_Num NOT LIKE '98%'
AND L.Part_Num <> 'NPN'
AND H.Com_Desc_Code = 'Parts'
AND Substring(L.Part_Num,4,1) <> 'Z'
AND L.HTS_NUM <> ISNULL(C.[CLASS_VALUE_1], '')
)
, CTE_2 AS (
SELECT
Z.[COMPOSITE_PART]
,Z.HTS_NUMBER
,MAX(Z.CREATED_DATE) AS MAX_DATE
FROM [TSI].[ZATS_BROKER_FEED] Z
INNER JOIN ADHOC.ATS_ESL L
ON L.PART_NUM = Z.COMPOSITE_PART
AND Z.CREATED_DATE <= L.ENTRY_DATE
WHERE
Z.SUB_ORG='FORD'
GROUP BY
Z.COMPOSITE_PART
,Z.HTS_NUMBER
)
For starters, you're missing the SELECT that comes after a CTE. Without it you'll always get an error.
March 5, 2018 at 12:42 pm
You're also either missing the closing parenthesis for the first CTE or having an extra opening parenthesis after the WHERE clause.
EDIT: Did I mention that you have several code smells that suggest a bad database design? If possible, you should correct them.
March 5, 2018 at 12:52 pm
You guys rock. I just had two CTEs , , , i didnt do the Select after CTE2 , , ,
Thanks!!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply