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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy