March 14, 2018 at 9:39 am
USE GTM_ODS
First, i declare the variable and set it . . . .
DECLARE @FTA VARCHAR (10)
SET @FTA= 'Korea'
IF @FTA = 'Korea'
Select...............known good code........................etc
ELSE IF @FTA = 'GSP'
Select ....................different known goodcode....................etc
I am getting a SYNTAX near ELSE Error. I am doing exactly what the guru did.
thoughts?
thanks
March 14, 2018 at 9:53 am
jeffshelix - Wednesday, March 14, 2018 9:39 AMUSE GTM_ODS
First, i declare the variable and set it . . . .
DECLARE @FTA VARCHAR (10)
SET @FTA= 'Korea'IF @FTA = 'Korea'
Select...............known good code........................etcELSE IF @FTA = 'GSP'
Select ....................different known goodcode....................etcI am getting a SYNTAX near ELSE Error. I am doing exactly what the guru did.
thoughts?
thanks
Well, let's see.... Nope can't see what you see.
What is the code and what is the error message or messages you are getting?
March 14, 2018 at 10:07 am
lots of code, but here you go.
I get a SYNtAX ERROR NEAR ELSE
USE GTM_ODS
DECLARE @FTA VARCHAR (10)
SET @FTA= 'KOREA'
if @FTA = 'GSP'
--Check for existing temp objects
IF OBJECT_ID('TEMPDB..#TEMP1') IS NOT NULL
DROP TABLE #TEMP1
-- Select * from #temp1
BEGIN
CREATE TABLE #TEMP1(CTRY varchar(50), ISO varchar (2), GSPA varchar (20), GSPLDBC Varchar (2), GSPASTAR Varchar(2))
INSERT INTO #TEMP1 VALUES ('Korea','KR','US - KOREA','','')
END -- FTA Temp Table into first temp table
;WITH CTE_1 as (
SELECT
PROD_ID
,D.[solicitation_type]
,D.SUPPLIER_ID
,D.SOLICITATION_ID
FROM
[SOLICIT].[gtm_doc] D
Inner join [SOLICIT].[gtm_doc_prod] P
On
D.[SOLICITATION_ID] = P.[SOLICITATION_ID]
AND D.[SUB_ORG] = P.[SUB_ORG]
AND D.[SOLICITATION_TYPE] = P.[SOLICITATION_TYPE]
INNER JOIN
TSC.EMS_EDI E
ON P.[PROD_ID] = E.edi_ref_num
AND D.SUPPLIER_id = substring(sys_varchar_3,116,5)
Where
D.[trade_program] = 'US - KOREA'
AND D.[solicitation_type] = 'REQUEST'
AND Datepart(year, [blanket_from_date]) = '2018'
AND D.sub_org = 'FORD'
)
, CTE_2 as (
SELECT
PROD_ID
,D.[solicitation_type]
,D.SUPPLIER_ID
,D.SOLICITATION_ID
FROM
[SOLICIT].[gtm_doc] D
Inner join [SOLICIT].[gtm_doc_prod] P
On
D.[SOLICITATION_ID] = P.[SOLICITATION_ID]
AND D.[SUB_ORG] = P.[SUB_ORG]
AND D.[SOLICITATION_TYPE] = P.[SOLICITATION_TYPE]
INNER JOIN
TSC.EMS_EDI E
ON P.[PROD_ID] = E.edi_ref_num
AND D.SUPPLIER_id = substring(sys_varchar_3,116,5)
Where
D.[trade_program] = 'US - KOREA'
AND D.[solicitation_type] = 'RESPONSE'
AND Datepart(year, [blanket_from_date]) = '2018'
AND D.sub_org = 'FORD'
)
,CTE_3 as (
SELECT TOP 1
D.[solicitation_id]
,P.PROD_ID
,D.SUPPLIER_ID
FROM
[SOLICIT].[gtm_doc] D
Inner join [SOLICIT].[gtm_doc_prod] P
On
D.[SOLICITATION_ID] = P.[SOLICITATION_ID]
AND D.[SUB_ORG] = P.[SUB_ORG]
AND D.[SOLICITATION_TYPE] = P.[SOLICITATION_TYPE]
INNER JOIN
TSC.EMS_EDI E
ON P.[PROD_ID] = E.edi_ref_num
AND D.SUPPLIER_id = substring(sys_varchar_3,116,5)
WHERE
D.[trade_program] = 'US - KOREA'
AND D.[solicitation_type] = 'REQUEST'
AND DATEPART(YEAR,[BLANKET_FROM_DATE]) = '2018'
AND D.SUB_ORG = 'FORD'
order by p.[SOLICITATION_ID] desc
)
--edi_ref_num prodid, substring(sys_varchar_3,116,5)
Select
'US - KOREA' as FTA
,substring(sys_varchar_3,116,5) as Supplier
,GetDate()+21 as RequestDueDate
,'' as RequestSupplierItemkey
,edi_ref_num as RequestClientItemKey
,P.DESC_EXPAND as Description
,CAST(SUBSTRING(substring(sys_varchar_3,172,15), PATINDEX('%[^0 ]%', substring(sys_varchar_3,172,15) + ' '), LEN(substring(sys_varchar_3,172,15))) as float)/1000000 as [Unverified Unit Price]
,min(cast(sent_date as date)) min_sentdate
,max(cast(sent_date as date)) max_sentdate
,sent_by as Site
,substring(sys_varchar_3,116,5) as ship_from
,substring(sys_varchar_3,123,5) as ship_to
,substring(sys_varchar_3,121,2) as ship_ctry
,CONCAT(T.GSPA,',', T.GSPASTAR,',',T.GSPLDBC) as FTA
,CTE_1.SOLICITATION_TYPE as Request
,CTE_1.[solicitation_id] as Request_Sol_ID
,CTE_2.SOLICITATION_TYPE as Response
,CTE_2.SOLICITATION_ID as Response_ID
,C.CLASS_VALUE_1 as HTS
,S.[STATUS]
,S.[SUPPLIER_NAME]
,P.Prod_ID
from TSC.EMS_EDI E
LEFT JOIN #TEMP1 T
ON T.ISO = substring(sys_varchar_3,121,2)
LEFT JOIN CTE_1
ON E.edi_ref_num = CTE_1.PROD_ID
AND substring(sys_varchar_3,116,5) = CTE_1.SUPPLIER_ID
LEFT JOIN CTE_2
ON edi_ref_num = CTE_2.[prod_id]
AND substring(sys_varchar_3,116,5) = CTE_2.SUPPLIER_ID
LEFT JOIN [TSC].[EMS_TVC_PROD_CLASS] C
ON E.edi_ref_num = 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'
LEFT JOIN [SOLICIT].[GTM_SUPPLIER] S
ON substring(sys_varchar_3,116,5) = S.[SUPPLIER_ID]
LEFT JOIN [TSC].[EMS_TVC_PROD] P
ON edi_ref_num = P.PROD_ID and p.SUB_ORG='Ford'
WHERE
edi_type = 'BCS_MQ_RECEIVED'
and edi_sub_type like 'JCST381U'
and sent_date > getdate( ) - 20
AND T.CTRY IS NOT NULL
GROUP by edi_ref_num , sent_by,
substring(sys_varchar_3,116,5) ,
substring(sys_varchar_3,123,5) ,
substring(sys_varchar_3,121,2) ,
substring(sys_varchar_3,172,15) ,
edi_sub_type,
T.Ctry,
T.GSPA,
T.GSPLDBC,
T.GSPASTAR,
T.ISO,
CTE_1.SOLICITATION_TYPE
,CTE_1.SOLICITATION_ID
,CTE_2.SOLICITATION_TYPE
,CTE_2.SOLICITATION_ID
,C.CLASS_VALUE_1
,S.[STATUS]
,S.[SUPPLIER_NAME]
,DESC_EXPAND
,P.Prod_ID
,edi_ref_num
ELSE IF @FTA = 'GSP'
--Check for existing temp objects
IF OBJECT_ID('TEMPDB..#TEMP1') IS NOT NULL
DROP TABLE #TEMP1
-- Select * from #temp1
BEGIN
CREATE TABLE #TEMP1(CTRY varchar(50), ISO varchar (2), GSPA varchar (2), GSPLDBC Varchar (2), GSPASTAR Varchar(2))
INSERT INTO #TEMP1 VALUES ('Afghanistan','AF','A','A+','')
INSERT INTO #TEMP1 VALUES ('Albania','AL','A','','')
INSERT INTO #TEMP1 VALUES ('Armenia','AM','A','','')
INSERT INTO #TEMP1 VALUES ('Angola','AO','A','A+','')
INSERT INTO #TEMP1 VALUES ('Argentina','AR','A','','A*')
INSERT INTO #TEMP1 VALUES ('Azerbaijan','AZ','A','','')
INSERT INTO #TEMP1 VALUES ('Bosnia and Hercegovina','BA','A','','')
INSERT INTO #TEMP1 VALUES ('Burkina Faso','BF','A','A+','')
INSERT INTO #TEMP1 VALUES ('Burundi','BI','A','A+','')
INSERT INTO #TEMP1 VALUES ('Benin','BJ','A','A+','')
INSERT INTO #TEMP1 VALUES ('Bolivia','BO','A','','')
INSERT INTO #TEMP1 VALUES ('Brazil','BR','A','','A*')
INSERT INTO #TEMP1 VALUES ('Bhutan','BT','A','A+','')
INSERT INTO #TEMP1 VALUES ('Burma','BU','A','A+','')
INSERT INTO #TEMP1 VALUES ('Botswana','BW','A','','')
INSERT INTO #TEMP1 VALUES ('Belize','BZ','A','','A*')
INSERT INTO #TEMP1 VALUES ('Congo (Kinshasa)','CD','A','A+','')
INSERT INTO #TEMP1 VALUES ('Central African Republic','CF','A','A+','')
INSERT INTO #TEMP1 VALUES ('Congo (Brazzaville)','CG','A','','')
INSERT INTO #TEMP1 VALUES ('Côte dIvoire','CI','A','','')
INSERT INTO #TEMP1 VALUES ('Cameroon','CM','A','','')
INSERT INTO #TEMP1 VALUES ('Cape Verde','CV','A','','')
INSERT INTO #TEMP1 VALUES ('Djibouti','DJ','A','A+','')
INSERT INTO #TEMP1 VALUES ('Dominica','DM','A','','')
INSERT INTO #TEMP1 VALUES ('Algeria','DZ','A','','')
INSERT INTO #TEMP1 VALUES ('Ecuador','EC','A','','A*')
INSERT INTO #TEMP1 VALUES ('Egypt','EG','A','','')
INSERT INTO #TEMP1 VALUES ('Eritrea','ER','A','','')
INSERT INTO #TEMP1 VALUES ('Ethiopia','ET','A','A+','')
INSERT INTO #TEMP1 VALUES ('Fiji','FJ','A','','')
INSERT INTO #TEMP1 VALUES ('Gabon','GA','A','','')
INSERT INTO #TEMP1 VALUES ('Grenada','GD','A','','')
INSERT INTO #TEMP1 VALUES ('Georgia','GE','A','','')
INSERT INTO #TEMP1 VALUES ('Ghana','GH','A','','')
INSERT INTO #TEMP1 VALUES ('Gambia','GM','A','A+','')
INSERT INTO #TEMP1 VALUES ('Guinea','GN','A','A+','')
INSERT INTO #TEMP1 VALUES ('Guinea-Bissau','GW','A','A+','')
INSERT INTO #TEMP1 VALUES ('Guyana','GY','A','','A*')
INSERT INTO #TEMP1 VALUES ('Haiti','HT','A','A+','')
INSERT INTO #TEMP1 VALUES ('Indonesia','ID','A','','A*')
INSERT INTO #TEMP1 VALUES ('India','IN','A','','A*')
INSERT INTO #TEMP1 VALUES ('Iraq','IQ','A','','')
INSERT INTO #TEMP1 VALUES ('Jamaica','JM','A','','A*')
INSERT INTO #TEMP1 VALUES ('Jordan','JO','A','','A*')
INSERT INTO #TEMP1 VALUES ('Kenya','KE','A','','')
INSERT INTO #TEMP1 VALUES ('Kyrgyzstan','KG','A','','')
INSERT INTO #TEMP1 VALUES ('Cambodia','KH','A','A+','')
INSERT INTO #TEMP1 VALUES ('Kiribati','KI','A','A+','')
INSERT INTO #TEMP1 VALUES ('Comoros','KM','A','A+','')
INSERT INTO #TEMP1 VALUES ('Kazakhstan','KZ','A','','A*')
INSERT INTO #TEMP1 VALUES ('Lebanon','LB','A','','')
INSERT INTO #TEMP1 VALUES ('Saint Lucia','LC','A','','')
INSERT INTO #TEMP1 VALUES ('Sri Lanka','LK','A','','')
INSERT INTO #TEMP1 VALUES ('Liberia','LR','A','A+','')
INSERT INTO #TEMP1 VALUES ('Lesotho','LS','A','A+','')
INSERT INTO #TEMP1 VALUES ('Moldova','MD','A','','')
INSERT INTO #TEMP1 VALUES ('Montenegro','ME','A','','')
INSERT INTO #TEMP1 VALUES ('Madagascar','MG','A','A+','')
INSERT INTO #TEMP1 VALUES ('Macedonia','MK','A','','')
INSERT INTO #TEMP1 VALUES ('Mali','ML','A','A+','')
INSERT INTO #TEMP1 VALUES ('Mongolia','MN','A','','')
INSERT INTO #TEMP1 VALUES ('Mauritania','MR','A','A+','')
INSERT INTO #TEMP1 VALUES ('Mauritius','MU','A','','')
INSERT INTO #TEMP1 VALUES ('Maldives','MV','A','','')
INSERT INTO #TEMP1 VALUES ('Malawi','MW','A','A+','')
INSERT INTO #TEMP1 VALUES ('Mozambique','MZ','A','A+','')
INSERT INTO #TEMP1 VALUES ('Namibia','NA','A','','')
INSERT INTO #TEMP1 VALUES ('Niger','NE','A','A+','')
INSERT INTO #TEMP1 VALUES ('Nigeria','NG','A','','')
INSERT INTO #TEMP1 VALUES ('Nepal','NP','A','A+','')
INSERT INTO #TEMP1 VALUES ('Papua New Guinea','PG','A','','')
INSERT INTO #TEMP1 VALUES ('Philippines','PH','A','','A*')
INSERT INTO #TEMP1 VALUES ('Pakistan','PK','A','','')
INSERT INTO #TEMP1 VALUES ('Paraguay','PY','A','','')
INSERT INTO #TEMP1 VALUES ('Serbia','RS','A','','')
INSERT INTO #TEMP1 VALUES ('Rwanda','RW','A','A+','')
INSERT INTO #TEMP1 VALUES ('Solomon Islands','SB','A','A+','')
INSERT INTO #TEMP1 VALUES ('Sierra Leone','SL','A','A+','')
INSERT INTO #TEMP1 VALUES ('Senegal','SN','A','A+','')
INSERT INTO #TEMP1 VALUES ('Somalia','SO','A','A+','')
INSERT INTO #TEMP1 VALUES ('Suriname','SR','A','','')
INSERT INTO #TEMP1 VALUES ('South Sudan','SS','A','A+','')
INSERT INTO #TEMP1 VALUES ('Sao Tome and Principe','ST','A','','')
INSERT INTO #TEMP1 VALUES ('Swaziland','SZ','A','','')
INSERT INTO #TEMP1 VALUES ('Chad','TD','A','A+','')
INSERT INTO #TEMP1 VALUES ('Togo','TG','A','A+','')
INSERT INTO #TEMP1 VALUES ('Thailand','TH','A','','A*')
INSERT INTO #TEMP1 VALUES ('Timor-Leste','TL','A','A+','')
INSERT INTO #TEMP1 VALUES ('Tunisia','TN','A','','')
INSERT INTO #TEMP1 VALUES ('Tonga','TO','A','','')
INSERT INTO #TEMP1 VALUES ('Turkey','TR','A','','A*')
INSERT INTO #TEMP1 VALUES ('Tuvalu','TV','A','A+','')
INSERT INTO #TEMP1 VALUES ('Tanzania','TZ','A','A+','')
INSERT INTO #TEMP1 VALUES ('Ukraine','UA','A','','')
INSERT INTO #TEMP1 VALUES ('Uganda','UG','A','A+','')
INSERT INTO #TEMP1 VALUES ('Uzbekistan','UZ','A','','')
INSERT INTO #TEMP1 VALUES ('St. Vincent and the Grenadines','VC','A','','')
INSERT INTO #TEMP1 VALUES ('Vanuatu','VU','A','A+','')
INSERT INTO #TEMP1 VALUES ('Samoa','WS','A','A+','')
INSERT INTO #TEMP1 VALUES ('Kosovo','XK','A','','')
INSERT INTO #TEMP1 VALUES ('Republic of Yemen','YE','A','A+','')
INSERT INTO #TEMP1 VALUES ('South Africa','ZA','D','','')
INSERT INTO #TEMP1 VALUES ('Zambia','ZM','A','A+','')
INSERT INTO #TEMP1 VALUES ('Zimbabwe','ZW','A','','')
END -- FTA Temp Table into first temp table
;WITH CTE_1 as (
SELECT
PROD_ID
,D.[solicitation_type]
,D.SUPPLIER_ID
,D.SOLICITATION_ID
FROM
[SOLICIT].[gtm_doc] D
Inner join [SOLICIT].[gtm_doc_prod] P
On
D.[SOLICITATION_ID] = P.[SOLICITATION_ID]
AND D.[SUB_ORG] = P.[SUB_ORG]
AND D.[SOLICITATION_TYPE] = P.[SOLICITATION_TYPE]
INNER JOIN
TSC.EMS_EDI E
ON P.[PROD_ID] = E.edi_ref_num
AND D.SUPPLIER_id = substring(sys_varchar_3,116,5)
Where
D.[trade_program] = 'GSP'
AND D.[solicitation_type] = 'REQUEST'
AND Datepart(year, [blanket_from_date]) = '2018'
AND D.sub_org = 'FORD'
)
, CTE_2 as (
SELECT
PROD_ID
,D.[solicitation_type]
,D.SUPPLIER_ID
,D.SOLICITATION_ID
FROM
[SOLICIT].[gtm_doc] D
Inner join [SOLICIT].[gtm_doc_prod] P
On
D.[SOLICITATION_ID] = P.[SOLICITATION_ID]
AND D.[SUB_ORG] = P.[SUB_ORG]
AND D.[SOLICITATION_TYPE] = P.[SOLICITATION_TYPE]
INNER JOIN
TSC.EMS_EDI E
ON P.[PROD_ID] = E.edi_ref_num
AND D.SUPPLIER_id = substring(sys_varchar_3,116,5)
Where
D.[trade_program] = 'GSP'
AND D.[solicitation_type] = 'RESPONSE'
AND Datepart(year, [blanket_from_date]) = '2018'
AND D.sub_org = 'FORD'
)
,CTE_3 as (
SELECT TOP 1
D.[solicitation_id]
,P.PROD_ID
,D.SUPPLIER_ID
FROM
[SOLICIT].[gtm_doc] D
Inner join [SOLICIT].[gtm_doc_prod] P
On
D.[SOLICITATION_ID] = P.[SOLICITATION_ID]
AND D.[SUB_ORG] = P.[SUB_ORG]
AND D.[SOLICITATION_TYPE] = P.[SOLICITATION_TYPE]
INNER JOIN
TSC.EMS_EDI E
ON P.[PROD_ID] = E.edi_ref_num
AND D.SUPPLIER_id = substring(sys_varchar_3,116,5)
WHERE
D.[trade_program] = 'GSP'
AND D.[solicitation_type] = 'REQUEST'
AND DATEPART(YEAR,[BLANKET_FROM_DATE]) = '2018'
AND D.SUB_ORG = 'FORD'
order by p.[SOLICITATION_ID] desc
)
--edi_ref_num prodid, substring(sys_varchar_3,116,5)
Select
'GSP'
,substring(sys_varchar_3,116,5) as Supplier
,GetDate()+21 as RequestDueDate
,'' as RequestSupplierItemkey
,edi_ref_num as RequestClientItemKey
,P.DESC_EXPAND as Description
,CAST(SUBSTRING(substring(sys_varchar_3,172,15), PATINDEX('%[^0 ]%', substring(sys_varchar_3,172,15) + ' '), LEN(substring(sys_varchar_3,172,15))) as float)/1000000 as [Unverified Unit Price]
,min(cast(sent_date as date)) min_sentdate
,max(cast(sent_date as date)) max_sentdate
,sent_by as Site
,substring(sys_varchar_3,116,5) as ship_from
,substring(sys_varchar_3,123,5) as ship_to
,substring(sys_varchar_3,121,2) as ship_ctry
,CONCAT(T.GSPA,',', T.GSPASTAR,',',T.GSPLDBC) as FTA
,CTE_1.SOLICITATION_TYPE as Request
,CTE_1.[solicitation_id] as Request_Sol_ID
,CTE_2.SOLICITATION_TYPE as Response
,CTE_2.SOLICITATION_ID as Response_ID
,C.CLASS_VALUE_1 as HTS
,S.[STATUS]
,S.[SUPPLIER_NAME]
,P.Prod_ID
from TSC.EMS_EDI E
LEFT JOIN #TEMP1 T
ON T.ISO = substring(sys_varchar_3,121,2)
LEFT JOIN CTE_1
ON E.edi_ref_num = CTE_1.PROD_ID
AND substring(sys_varchar_3,116,5) = CTE_1.SUPPLIER_ID
LEFT JOIN CTE_2
ON edi_ref_num = CTE_2.[prod_id]
AND substring(sys_varchar_3,116,5) = CTE_2.SUPPLIER_ID
LEFT JOIN [TSC].[EMS_TVC_PROD_CLASS] C
ON E.edi_ref_num = 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'
LEFT JOIN [SOLICIT].[GTM_SUPPLIER] S
ON substring(sys_varchar_3,116,5) = S.[SUPPLIER_ID]
LEFT JOIN [TSC].[EMS_TVC_PROD] P
ON edi_ref_num = P.PROD_ID and p.SUB_ORG='Ford'
WHERE
edi_type = 'BCS_MQ_RECEIVED'
and edi_sub_type like 'JCST381U'
and sent_date > getdate( ) - 20
AND T.CTRY IS NOT NULL
GROUP by edi_ref_num , sent_by,
substring(sys_varchar_3,116,5) ,
substring(sys_varchar_3,123,5) ,
substring(sys_varchar_3,121,2) ,
substring(sys_varchar_3,172,15) ,
edi_sub_type,
T.Ctry,
T.GSPA,
T.GSPLDBC,
T.GSPASTAR,
T.ISO,
CTE_1.SOLICITATION_TYPE
,CTE_1.SOLICITATION_ID
,CTE_2.SOLICITATION_TYPE
,CTE_2.SOLICITATION_ID
,C.CLASS_VALUE_1
,S.[STATUS]
,S.[SUPPLIER_NAME]
,DESC_EXPAND
,P.Prod_ID
,edi_ref_num
March 14, 2018 at 10:14 am
jeffshelix - Wednesday, March 14, 2018 10:07 AMlots of code, but here you go.
I get a SYNtAX ERROR NEAR ELSE
USE GTM_ODSDECLARE @FTA VARCHAR (10)
SET @FTA= 'KOREA'
if @FTA = 'GSP'
--Check for existing temp objects
IF OBJECT_ID('TEMPDB..#TEMP1') IS NOT NULL
DROP TABLE #TEMP1-- Select * from #temp1
BEGIN
CREATE TABLE #TEMP1(CTRY varchar(50), ISO varchar (2), GSPA varchar (20), GSPLDBC Varchar (2), GSPASTAR Varchar(2))
INSERT INTO #TEMP1 VALUES ('Korea','KR','US - KOREA','','')
END -- FTA Temp Table into first temp table
;WITH CTE_1 as (
SELECT
PROD_ID
,D.[solicitation_type]
,D.SUPPLIER_ID
,D.SOLICITATION_ID
FROM
[SOLICIT].[gtm_doc] D
Inner join [SOLICIT].[gtm_doc_prod] P
On
D.[SOLICITATION_ID] = P.[SOLICITATION_ID]
AND D.[SUB_ORG] = P.[SUB_ORG]
AND D.[SOLICITATION_TYPE] = P.[SOLICITATION_TYPE]
INNER JOIN
TSC.EMS_EDI E
ON P.[PROD_ID] = E.edi_ref_num
AND D.SUPPLIER_id = substring(sys_varchar_3,116,5)
Where
D.[trade_program] = 'US - KOREA'
AND D.[solicitation_type] = 'REQUEST'
AND Datepart(year, [blanket_from_date]) = '2018'
AND D.sub_org = 'FORD'
)
, CTE_2 as (
SELECT
PROD_ID
,D.[solicitation_type]
,D.SUPPLIER_ID
,D.SOLICITATION_ID
FROM
[SOLICIT].[gtm_doc] D
Inner join [SOLICIT].[gtm_doc_prod] P
On
D.[SOLICITATION_ID] = P.[SOLICITATION_ID]
AND D.[SUB_ORG] = P.[SUB_ORG]
AND D.[SOLICITATION_TYPE] = P.[SOLICITATION_TYPE]
INNER JOIN
TSC.EMS_EDI E
ON P.[PROD_ID] = E.edi_ref_num
AND D.SUPPLIER_id = substring(sys_varchar_3,116,5)
Where
D.[trade_program] = 'US - KOREA'
AND D.[solicitation_type] = 'RESPONSE'
AND Datepart(year, [blanket_from_date]) = '2018'
AND D.sub_org = 'FORD'
)
,CTE_3 as (
SELECT TOP 1
D.[solicitation_id]
,P.PROD_ID
,D.SUPPLIER_ID
FROM
[SOLICIT].[gtm_doc] D
Inner join [SOLICIT].[gtm_doc_prod] P
On
D.[SOLICITATION_ID] = P.[SOLICITATION_ID]
AND D.[SUB_ORG] = P.[SUB_ORG]
AND D.[SOLICITATION_TYPE] = P.[SOLICITATION_TYPE]
INNER JOIN
TSC.EMS_EDI E
ON P.[PROD_ID] = E.edi_ref_num
AND D.SUPPLIER_id = substring(sys_varchar_3,116,5)
WHERE
D.[trade_program] = 'US - KOREA'
AND D.[solicitation_type] = 'REQUEST'
AND DATEPART(YEAR,[BLANKET_FROM_DATE]) = '2018'
AND D.SUB_ORG = 'FORD'
order by p.[SOLICITATION_ID] desc
)--edi_ref_num prodid, substring(sys_varchar_3,116,5)
Select
'US - KOREA' as FTA
,substring(sys_varchar_3,116,5) as Supplier
,GetDate()+21 as RequestDueDate
,'' as RequestSupplierItemkey
,edi_ref_num as RequestClientItemKey
,P.DESC_EXPAND as Description
,CAST(SUBSTRING(substring(sys_varchar_3,172,15), PATINDEX('%[^0 ]%', substring(sys_varchar_3,172,15) + ' '), LEN(substring(sys_varchar_3,172,15))) as float)/1000000 as [Unverified Unit Price]
,min(cast(sent_date as date)) min_sentdate
,max(cast(sent_date as date)) max_sentdate
,sent_by as Site
,substring(sys_varchar_3,116,5) as ship_from
,substring(sys_varchar_3,123,5) as ship_to
,substring(sys_varchar_3,121,2) as ship_ctry
,CONCAT(T.GSPA,',', T.GSPASTAR,',',T.GSPLDBC) as FTA
,CTE_1.SOLICITATION_TYPE as Request
,CTE_1.[solicitation_id] as Request_Sol_ID
,CTE_2.SOLICITATION_TYPE as Response
,CTE_2.SOLICITATION_ID as Response_ID
,C.CLASS_VALUE_1 as HTS
,S.[STATUS]
,S.[SUPPLIER_NAME]
,P.Prod_ID
from TSC.EMS_EDI E
LEFT JOIN #TEMP1 T
ON T.ISO = substring(sys_varchar_3,121,2)
LEFT JOIN CTE_1
ON E.edi_ref_num = CTE_1.PROD_ID
AND substring(sys_varchar_3,116,5) = CTE_1.SUPPLIER_ID
LEFT JOIN CTE_2
ON edi_ref_num = CTE_2.[prod_id]
AND substring(sys_varchar_3,116,5) = CTE_2.SUPPLIER_ID
LEFT JOIN [TSC].[EMS_TVC_PROD_CLASS] C
ON E.edi_ref_num = 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'
LEFT JOIN [SOLICIT].[GTM_SUPPLIER] S
ON substring(sys_varchar_3,116,5) = S.[SUPPLIER_ID]
LEFT JOIN [TSC].[EMS_TVC_PROD] P
ON edi_ref_num = P.PROD_ID and p.SUB_ORG='Ford'WHERE
edi_type = 'BCS_MQ_RECEIVED'
and edi_sub_type like 'JCST381U'
and sent_date > getdate( ) - 20
AND T.CTRY IS NOT NULLGROUP by edi_ref_num , sent_by,
substring(sys_varchar_3,116,5) ,
substring(sys_varchar_3,123,5) ,
substring(sys_varchar_3,121,2) ,
substring(sys_varchar_3,172,15) ,
edi_sub_type,
T.Ctry,
T.GSPA,
T.GSPLDBC,
T.GSPASTAR,
T.ISO,
CTE_1.SOLICITATION_TYPE
,CTE_1.SOLICITATION_ID
,CTE_2.SOLICITATION_TYPE
,CTE_2.SOLICITATION_ID
,C.CLASS_VALUE_1
,S.[STATUS]
,S.[SUPPLIER_NAME]
,DESC_EXPAND
,P.Prod_ID
,edi_ref_numELSE IF @FTA = 'GSP'
--Check for existing temp objects
IF OBJECT_ID('TEMPDB..#TEMP1') IS NOT NULL
DROP TABLE #TEMP1-- Select * from #temp1
BEGIN
CREATE TABLE #TEMP1(CTRY varchar(50), ISO varchar (2), GSPA varchar (2), GSPLDBC Varchar (2), GSPASTAR Varchar(2))
INSERT INTO #TEMP1 VALUES ('Afghanistan','AF','A','A+','')
INSERT INTO #TEMP1 VALUES ('Albania','AL','A','','')
INSERT INTO #TEMP1 VALUES ('Armenia','AM','A','','')
INSERT INTO #TEMP1 VALUES ('Angola','AO','A','A+','')
INSERT INTO #TEMP1 VALUES ('Argentina','AR','A','','A*')
INSERT INTO #TEMP1 VALUES ('Azerbaijan','AZ','A','','')
INSERT INTO #TEMP1 VALUES ('Bosnia and Hercegovina','BA','A','','')
INSERT INTO #TEMP1 VALUES ('Burkina Faso','BF','A','A+','')
INSERT INTO #TEMP1 VALUES ('Burundi','BI','A','A+','')
INSERT INTO #TEMP1 VALUES ('Benin','BJ','A','A+','')
INSERT INTO #TEMP1 VALUES ('Bolivia','BO','A','','')
INSERT INTO #TEMP1 VALUES ('Brazil','BR','A','','A*')
INSERT INTO #TEMP1 VALUES ('Bhutan','BT','A','A+','')
INSERT INTO #TEMP1 VALUES ('Burma','BU','A','A+','')
INSERT INTO #TEMP1 VALUES ('Botswana','BW','A','','')
INSERT INTO #TEMP1 VALUES ('Belize','BZ','A','','A*')
INSERT INTO #TEMP1 VALUES ('Congo (Kinshasa)','CD','A','A+','')
INSERT INTO #TEMP1 VALUES ('Central African Republic','CF','A','A+','')
INSERT INTO #TEMP1 VALUES ('Congo (Brazzaville)','CG','A','','')
INSERT INTO #TEMP1 VALUES ('Côte dIvoire','CI','A','','')
INSERT INTO #TEMP1 VALUES ('Cameroon','CM','A','','')
INSERT INTO #TEMP1 VALUES ('Cape Verde','CV','A','','')
INSERT INTO #TEMP1 VALUES ('Djibouti','DJ','A','A+','')
INSERT INTO #TEMP1 VALUES ('Dominica','DM','A','','')
INSERT INTO #TEMP1 VALUES ('Algeria','DZ','A','','')
INSERT INTO #TEMP1 VALUES ('Ecuador','EC','A','','A*')
INSERT INTO #TEMP1 VALUES ('Egypt','EG','A','','')
INSERT INTO #TEMP1 VALUES ('Eritrea','ER','A','','')
INSERT INTO #TEMP1 VALUES ('Ethiopia','ET','A','A+','')
INSERT INTO #TEMP1 VALUES ('Fiji','FJ','A','','')
INSERT INTO #TEMP1 VALUES ('Gabon','GA','A','','')
INSERT INTO #TEMP1 VALUES ('Grenada','GD','A','','')
INSERT INTO #TEMP1 VALUES ('Georgia','GE','A','','')
INSERT INTO #TEMP1 VALUES ('Ghana','GH','A','','')
INSERT INTO #TEMP1 VALUES ('Gambia','GM','A','A+','')
INSERT INTO #TEMP1 VALUES ('Guinea','GN','A','A+','')
INSERT INTO #TEMP1 VALUES ('Guinea-Bissau','GW','A','A+','')
INSERT INTO #TEMP1 VALUES ('Guyana','GY','A','','A*')
INSERT INTO #TEMP1 VALUES ('Haiti','HT','A','A+','')
INSERT INTO #TEMP1 VALUES ('Indonesia','ID','A','','A*')
INSERT INTO #TEMP1 VALUES ('India','IN','A','','A*')
INSERT INTO #TEMP1 VALUES ('Iraq','IQ','A','','')
INSERT INTO #TEMP1 VALUES ('Jamaica','JM','A','','A*')
INSERT INTO #TEMP1 VALUES ('Jordan','JO','A','','A*')
INSERT INTO #TEMP1 VALUES ('Kenya','KE','A','','')
INSERT INTO #TEMP1 VALUES ('Kyrgyzstan','KG','A','','')
INSERT INTO #TEMP1 VALUES ('Cambodia','KH','A','A+','')
INSERT INTO #TEMP1 VALUES ('Kiribati','KI','A','A+','')
INSERT INTO #TEMP1 VALUES ('Comoros','KM','A','A+','')
INSERT INTO #TEMP1 VALUES ('Kazakhstan','KZ','A','','A*')
INSERT INTO #TEMP1 VALUES ('Lebanon','LB','A','','')
INSERT INTO #TEMP1 VALUES ('Saint Lucia','LC','A','','')
INSERT INTO #TEMP1 VALUES ('Sri Lanka','LK','A','','')
INSERT INTO #TEMP1 VALUES ('Liberia','LR','A','A+','')
INSERT INTO #TEMP1 VALUES ('Lesotho','LS','A','A+','')
INSERT INTO #TEMP1 VALUES ('Moldova','MD','A','','')
INSERT INTO #TEMP1 VALUES ('Montenegro','ME','A','','')
INSERT INTO #TEMP1 VALUES ('Madagascar','MG','A','A+','')
INSERT INTO #TEMP1 VALUES ('Macedonia','MK','A','','')
INSERT INTO #TEMP1 VALUES ('Mali','ML','A','A+','')
INSERT INTO #TEMP1 VALUES ('Mongolia','MN','A','','')
INSERT INTO #TEMP1 VALUES ('Mauritania','MR','A','A+','')
INSERT INTO #TEMP1 VALUES ('Mauritius','MU','A','','')
INSERT INTO #TEMP1 VALUES ('Maldives','MV','A','','')
INSERT INTO #TEMP1 VALUES ('Malawi','MW','A','A+','')
INSERT INTO #TEMP1 VALUES ('Mozambique','MZ','A','A+','')
INSERT INTO #TEMP1 VALUES ('Namibia','NA','A','','')
INSERT INTO #TEMP1 VALUES ('Niger','NE','A','A+','')
INSERT INTO #TEMP1 VALUES ('Nigeria','NG','A','','')
INSERT INTO #TEMP1 VALUES ('Nepal','NP','A','A+','')
INSERT INTO #TEMP1 VALUES ('Papua New Guinea','PG','A','','')
INSERT INTO #TEMP1 VALUES ('Philippines','PH','A','','A*')
INSERT INTO #TEMP1 VALUES ('Pakistan','PK','A','','')
INSERT INTO #TEMP1 VALUES ('Paraguay','PY','A','','')
INSERT INTO #TEMP1 VALUES ('Serbia','RS','A','','')
INSERT INTO #TEMP1 VALUES ('Rwanda','RW','A','A+','')
INSERT INTO #TEMP1 VALUES ('Solomon Islands','SB','A','A+','')
INSERT INTO #TEMP1 VALUES ('Sierra Leone','SL','A','A+','')
INSERT INTO #TEMP1 VALUES ('Senegal','SN','A','A+','')
INSERT INTO #TEMP1 VALUES ('Somalia','SO','A','A+','')
INSERT INTO #TEMP1 VALUES ('Suriname','SR','A','','')
INSERT INTO #TEMP1 VALUES ('South Sudan','SS','A','A+','')
INSERT INTO #TEMP1 VALUES ('Sao Tome and Principe','ST','A','','')
INSERT INTO #TEMP1 VALUES ('Swaziland','SZ','A','','')
INSERT INTO #TEMP1 VALUES ('Chad','TD','A','A+','')
INSERT INTO #TEMP1 VALUES ('Togo','TG','A','A+','')
INSERT INTO #TEMP1 VALUES ('Thailand','TH','A','','A*')
INSERT INTO #TEMP1 VALUES ('Timor-Leste','TL','A','A+','')
INSERT INTO #TEMP1 VALUES ('Tunisia','TN','A','','')
INSERT INTO #TEMP1 VALUES ('Tonga','TO','A','','')
INSERT INTO #TEMP1 VALUES ('Turkey','TR','A','','A*')
INSERT INTO #TEMP1 VALUES ('Tuvalu','TV','A','A+','')
INSERT INTO #TEMP1 VALUES ('Tanzania','TZ','A','A+','')
INSERT INTO #TEMP1 VALUES ('Ukraine','UA','A','','')
INSERT INTO #TEMP1 VALUES ('Uganda','UG','A','A+','')
INSERT INTO #TEMP1 VALUES ('Uzbekistan','UZ','A','','')
INSERT INTO #TEMP1 VALUES ('St. Vincent and the Grenadines','VC','A','','')
INSERT INTO #TEMP1 VALUES ('Vanuatu','VU','A','A+','')
INSERT INTO #TEMP1 VALUES ('Samoa','WS','A','A+','')
INSERT INTO #TEMP1 VALUES ('Kosovo','XK','A','','')
INSERT INTO #TEMP1 VALUES ('Republic of Yemen','YE','A','A+','')
INSERT INTO #TEMP1 VALUES ('South Africa','ZA','D','','')
INSERT INTO #TEMP1 VALUES ('Zambia','ZM','A','A+','')
INSERT INTO #TEMP1 VALUES ('Zimbabwe','ZW','A','','')
END -- FTA Temp Table into first temp table;WITH CTE_1 as (
SELECT
PROD_ID
,D.[solicitation_type]
,D.SUPPLIER_ID
,D.SOLICITATION_ID
FROM
[SOLICIT].[gtm_doc] D
Inner join [SOLICIT].[gtm_doc_prod] P
On
D.[SOLICITATION_ID] = P.[SOLICITATION_ID]
AND D.[SUB_ORG] = P.[SUB_ORG]
AND D.[SOLICITATION_TYPE] = P.[SOLICITATION_TYPE]
INNER JOIN
TSC.EMS_EDI E
ON P.[PROD_ID] = E.edi_ref_num
AND D.SUPPLIER_id = substring(sys_varchar_3,116,5)
Where
D.[trade_program] = 'GSP'
AND D.[solicitation_type] = 'REQUEST'
AND Datepart(year, [blanket_from_date]) = '2018'
AND D.sub_org = 'FORD'
)
, CTE_2 as (
SELECT
PROD_ID
,D.[solicitation_type]
,D.SUPPLIER_ID
,D.SOLICITATION_ID
FROM
[SOLICIT].[gtm_doc] D
Inner join [SOLICIT].[gtm_doc_prod] P
On
D.[SOLICITATION_ID] = P.[SOLICITATION_ID]
AND D.[SUB_ORG] = P.[SUB_ORG]
AND D.[SOLICITATION_TYPE] = P.[SOLICITATION_TYPE]
INNER JOIN
TSC.EMS_EDI E
ON P.[PROD_ID] = E.edi_ref_num
AND D.SUPPLIER_id = substring(sys_varchar_3,116,5)
Where
D.[trade_program] = 'GSP'
AND D.[solicitation_type] = 'RESPONSE'
AND Datepart(year, [blanket_from_date]) = '2018'
AND D.sub_org = 'FORD'
)
,CTE_3 as (
SELECT TOP 1
D.[solicitation_id]
,P.PROD_ID
,D.SUPPLIER_ID
FROM
[SOLICIT].[gtm_doc] D
Inner join [SOLICIT].[gtm_doc_prod] P
On
D.[SOLICITATION_ID] = P.[SOLICITATION_ID]
AND D.[SUB_ORG] = P.[SUB_ORG]
AND D.[SOLICITATION_TYPE] = P.[SOLICITATION_TYPE]
INNER JOIN
TSC.EMS_EDI E
ON P.[PROD_ID] = E.edi_ref_num
AND D.SUPPLIER_id = substring(sys_varchar_3,116,5)
WHERE
D.[trade_program] = 'GSP'
AND D.[solicitation_type] = 'REQUEST'
AND DATEPART(YEAR,[BLANKET_FROM_DATE]) = '2018'
AND D.SUB_ORG = 'FORD'
order by p.[SOLICITATION_ID] desc
)--edi_ref_num prodid, substring(sys_varchar_3,116,5)
Select
'GSP'
,substring(sys_varchar_3,116,5) as Supplier
,GetDate()+21 as RequestDueDate
,'' as RequestSupplierItemkey
,edi_ref_num as RequestClientItemKey
,P.DESC_EXPAND as Description
,CAST(SUBSTRING(substring(sys_varchar_3,172,15), PATINDEX('%[^0 ]%', substring(sys_varchar_3,172,15) + ' '), LEN(substring(sys_varchar_3,172,15))) as float)/1000000 as [Unverified Unit Price]
,min(cast(sent_date as date)) min_sentdate
,max(cast(sent_date as date)) max_sentdate
,sent_by as Site
,substring(sys_varchar_3,116,5) as ship_from
,substring(sys_varchar_3,123,5) as ship_to
,substring(sys_varchar_3,121,2) as ship_ctry
,CONCAT(T.GSPA,',', T.GSPASTAR,',',T.GSPLDBC) as FTA
,CTE_1.SOLICITATION_TYPE as Request
,CTE_1.[solicitation_id] as Request_Sol_ID
,CTE_2.SOLICITATION_TYPE as Response
,CTE_2.SOLICITATION_ID as Response_ID
,C.CLASS_VALUE_1 as HTS
,S.[STATUS]
,S.[SUPPLIER_NAME]
,P.Prod_ID
from TSC.EMS_EDI E
LEFT JOIN #TEMP1 T
ON T.ISO = substring(sys_varchar_3,121,2)
LEFT JOIN CTE_1
ON E.edi_ref_num = CTE_1.PROD_ID
AND substring(sys_varchar_3,116,5) = CTE_1.SUPPLIER_ID
LEFT JOIN CTE_2
ON edi_ref_num = CTE_2.[prod_id]
AND substring(sys_varchar_3,116,5) = CTE_2.SUPPLIER_ID
LEFT JOIN [TSC].[EMS_TVC_PROD_CLASS] C
ON E.edi_ref_num = 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'
LEFT JOIN [SOLICIT].[GTM_SUPPLIER] S
ON substring(sys_varchar_3,116,5) = S.[SUPPLIER_ID]
LEFT JOIN [TSC].[EMS_TVC_PROD] P
ON edi_ref_num = P.PROD_ID and p.SUB_ORG='Ford'WHERE
edi_type = 'BCS_MQ_RECEIVED'
and edi_sub_type like 'JCST381U'
and sent_date > getdate( ) - 20
AND T.CTRY IS NOT NULLGROUP by edi_ref_num , sent_by,
substring(sys_varchar_3,116,5) ,
substring(sys_varchar_3,123,5) ,
substring(sys_varchar_3,121,2) ,
substring(sys_varchar_3,172,15) ,
edi_sub_type,
T.Ctry,
T.GSPA,
T.GSPLDBC,
T.GSPASTAR,
T.ISO,
CTE_1.SOLICITATION_TYPE
,CTE_1.SOLICITATION_ID
,CTE_2.SOLICITATION_TYPE
,CTE_2.SOLICITATION_ID
,C.CLASS_VALUE_1
,S.[STATUS]
,S.[SUPPLIER_NAME]
,DESC_EXPAND
,P.Prod_ID
,edi_ref_num
The error message just says syntax error, that's it. Really?
March 14, 2018 at 10:15 am
BEGIN needs to come immediately after IF or ELSE IF, and the corresponding END needs to come right at the end or immediately before the next ELSE IF. Hope that makes sense.
John
March 14, 2018 at 10:27 am
Okay, you were missing some BEGIN END blocks in the IF STATEMENTS. Also, CTE's do not start with a semicolon ( ; ), the previous statement must be terminated with a semicolon ( ; ). You should get in the habit of terminating all statements with a semicolon ( ; ).
Here is your code with some changes, you will have to determine if I caught the syntax errors.
USE GTM_ODS
DECLARE @FTA VARCHAR (10)
SET @FTA= 'KOREA'
if @FTA = 'GSP'
begin
--Check for existing temp objects
IF OBJECT_ID('TEMPDB..#TEMP1') IS NOT NULL
DROP TABLE #TEMP1;
-- Select * from #temp1
BEGIN
CREATE TABLE #TEMP1(CTRY varchar(50), ISO varchar (2), GSPA varchar (20), GSPLDBC Varchar (2), GSPASTAR Varchar(2));
INSERT INTO #TEMP1 VALUES ('Korea','KR','US - KOREA','','');
END -- FTA Temp Table into first temp table
WITH CTE_1 as (
SELECT
PROD_ID
,D.[solicitation_type]
,D.SUPPLIER_ID
,D.SOLICITATION_ID
FROM
[SOLICIT].[gtm_doc] D
Inner join [SOLICIT].[gtm_doc_prod] P
ON
D.[SOLICITATION_ID] = P.[SOLICITATION_ID]
AND D.[SUB_ORG] = P.[SUB_ORG]
AND D.[SOLICITATION_TYPE] = P.[SOLICITATION_TYPE]
INNER JOIN
TSC.EMS_EDI E
ON P.[PROD_ID] = E.edi_ref_num
AND D.SUPPLIER_id = substring(sys_varchar_3,116,5)
Where
D.[trade_program] = 'US - KOREA'
AND D.[solicitation_type] = 'REQUEST'
AND Datepart(year, [blanket_from_date]) = '2018'
AND D.sub_org = 'FORD'
)
, CTE_2 as (
SELECT
PROD_ID
,D.[solicitation_type]
,D.SUPPLIER_ID
,D.SOLICITATION_ID
FROM
[SOLICIT].[gtm_doc] D
Inner join [SOLICIT].[gtm_doc_prod] P
On
D.[SOLICITATION_ID] = P.[SOLICITATION_ID]
AND D.[SUB_ORG] = P.[SUB_ORG]
AND D.[SOLICITATION_TYPE] = P.[SOLICITATION_TYPE]
INNER JOIN
TSC.EMS_EDI E
ON P.[PROD_ID] = E.edi_ref_num
AND D.SUPPLIER_id = substring(sys_varchar_3,116,5)
Where
D.[trade_program] = 'US - KOREA'
AND D.[solicitation_type] = 'RESPONSE'
AND Datepart(year, [blanket_from_date]) = '2018'
AND D.sub_org = 'FORD'
)
,CTE_3 as (
SELECT TOP 1
D.[solicitation_id]
,P.PROD_ID
,D.SUPPLIER_ID
FROM
[SOLICIT].[gtm_doc] D
Inner join [SOLICIT].[gtm_doc_prod] P
On
D.[SOLICITATION_ID] = P.[SOLICITATION_ID]
AND D.[SUB_ORG] = P.[SUB_ORG]
AND D.[SOLICITATION_TYPE] = P.[SOLICITATION_TYPE]
INNER JOIN
TSC.EMS_EDI E
ON P.[PROD_ID] = E.edi_ref_num
AND D.SUPPLIER_id = substring(sys_varchar_3,116,5)
WHERE
D.[trade_program] = 'US - KOREA'
AND D.[solicitation_type] = 'REQUEST'
AND DATEPART(YEAR,[BLANKET_FROM_DATE]) = '2018'
AND D.SUB_ORG = 'FORD'
order by p.[SOLICITATION_ID] desc
)
--edi_ref_num prodid, substring(sys_varchar_3,116,5)
Select
'US - KOREA' as FTA
,substring(sys_varchar_3,116,5) as Supplier
,GetDate()+21 as RequestDueDate
,'' as RequestSupplierItemkey
,edi_ref_num as RequestClientItemKey
,P.DESC_EXPAND as Description
,CAST(SUBSTRING(substring(sys_varchar_3,172,15), PATINDEX('%[^0 ]%', substring(sys_varchar_3,172,15) + ' '), LEN(substring(sys_varchar_3,172,15))) as float)/1000000 as [Unverified Unit Price]
,min(cast(sent_date as date)) min_sentdate
,max(cast(sent_date as date)) max_sentdate
,sent_by as Site
,substring(sys_varchar_3,116,5) as ship_from
,substring(sys_varchar_3,123,5) as ship_to
,substring(sys_varchar_3,121,2) as ship_ctry
,CONCAT(T.GSPA,',', T.GSPASTAR,',',T.GSPLDBC) as FTA
,CTE_1.SOLICITATION_TYPE as Request
,CTE_1.[solicitation_id] as Request_Sol_ID
,CTE_2.SOLICITATION_TYPE as Response
,CTE_2.SOLICITATION_ID as Response_ID
,C.CLASS_VALUE_1 as HTS
,S.[STATUS]
,S.[SUPPLIER_NAME]
,P.Prod_ID
from TSC.EMS_EDI E
LEFT JOIN #TEMP1 T
ON T.ISO = substring(sys_varchar_3,121,2)
LEFT JOIN CTE_1
ON E.edi_ref_num = CTE_1.PROD_ID
AND substring(sys_varchar_3,116,5) = CTE_1.SUPPLIER_ID
LEFT JOIN CTE_2
ON edi_ref_num = CTE_2.[prod_id]
AND substring(sys_varchar_3,116,5) = CTE_2.SUPPLIER_ID
LEFT JOIN [TSC].[EMS_TVC_PROD_CLASS] C
ON E.edi_ref_num = 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'
LEFT JOIN [SOLICIT].[GTM_SUPPLIER] S
ON substring(sys_varchar_3,116,5) = S.[SUPPLIER_ID]
LEFT JOIN [TSC].[EMS_TVC_PROD] P
ON edi_ref_num = P.PROD_ID and p.SUB_ORG='Ford'
WHERE
edi_type = 'BCS_MQ_RECEIVED'
and edi_sub_type like 'JCST381U'
and sent_date > getdate( ) - 20
AND T.CTRY IS NOT NULL
GROUP by edi_ref_num , sent_by,
substring(sys_varchar_3,116,5) ,
substring(sys_varchar_3,123,5) ,
substring(sys_varchar_3,121,2) ,
substring(sys_varchar_3,172,15) ,
edi_sub_type,
T.Ctry,
T.GSPA,
T.GSPLDBC,
T.GSPASTAR,
T.ISO,
CTE_1.SOLICITATION_TYPE
,CTE_1.SOLICITATION_ID
,CTE_2.SOLICITATION_TYPE
,CTE_2.SOLICITATION_ID
,C.CLASS_VALUE_1
,S.[STATUS]
,S.[SUPPLIER_NAME]
,DESC_EXPAND
,P.Prod_ID
,edi_ref_num
end
ELSE IF @FTA = 'GSP'
begin
--Check for existing temp objects
IF OBJECT_ID('TEMPDB..#TEMP1') IS NOT NULL
DROP TABLE #TEMP1
-- Select * from #temp1
BEGIN
CREATE TABLE #TEMP1(CTRY varchar(50), ISO varchar (2), GSPA varchar (2), GSPLDBC Varchar (2), GSPASTAR Varchar(2));
INSERT INTO #TEMP1
VALUES ('Afghanistan','AF','A','A+','')
,('Albania','AL','A','','')
,('Armenia','AM','A','','')
,('Angola','AO','A','A+','')
,('Argentina','AR','A','','A*')
,('Azerbaijan','AZ','A','','')
,('Bosnia and Hercegovina','BA','A','','')
,('Burkina Faso','BF','A','A+','')
,('Burundi','BI','A','A+','')
,('Benin','BJ','A','A+','')
,('Bolivia','BO','A','','')
,('Brazil','BR','A','','A*')
,('Bhutan','BT','A','A+','')
,('Burma','BU','A','A+','')
,('Botswana','BW','A','','')
,('Belize','BZ','A','','A*')
,('Congo (Kinshasa)','CD','A','A+','')
,('Central African Republic','CF','A','A+','')
,('Congo (Brazzaville)','CG','A','','')
,('Côte dIvoire','CI','A','','')
,('Cameroon','CM','A','','')
,('Cape Verde','CV','A','','')
,('Djibouti','DJ','A','A+','')
,('Dominica','DM','A','','')
,('Algeria','DZ','A','','')
,('Ecuador','EC','A','','A*')
,('Egypt','EG','A','','')
,('Eritrea','ER','A','','')
,('Ethiopia','ET','A','A+','')
,('Fiji','FJ','A','','')
,('Gabon','GA','A','','')
,('Grenada','GD','A','','')
,('Georgia','GE','A','','')
,('Ghana','GH','A','','')
,('Gambia','GM','A','A+','')
,('Guinea','GN','A','A+','')
,('Guinea-Bissau','GW','A','A+','')
,('Guyana','GY','A','','A*')
,('Haiti','HT','A','A+','')
,('Indonesia','ID','A','','A*')
,('India','IN','A','','A*')
,('Iraq','IQ','A','','')
,('Jamaica','JM','A','','A*')
,('Jordan','JO','A','','A*')
,('Kenya','KE','A','','')
,('Kyrgyzstan','KG','A','','')
,('Cambodia','KH','A','A+','')
,('Kiribati','KI','A','A+','')
,('Comoros','KM','A','A+','')
,('Kazakhstan','KZ','A','','A*')
,('Lebanon','LB','A','','')
,('Saint Lucia','LC','A','','')
,('Sri Lanka','LK','A','','')
,('Liberia','LR','A','A+','')
,('Lesotho','LS','A','A+','')
,('Moldova','MD','A','','')
,('Montenegro','ME','A','','')
,('Madagascar','MG','A','A+','')
,('Macedonia','MK','A','','')
,('Mali','ML','A','A+','')
,('Mongolia','MN','A','','')
,('Mauritania','MR','A','A+','')
,('Mauritius','MU','A','','')
,('Maldives','MV','A','','')
,('Malawi','MW','A','A+','')
,('Mozambique','MZ','A','A+','')
,('Namibia','NA','A','','')
,('Niger','NE','A','A+','')
,('Nigeria','NG','A','','')
,('Nepal','NP','A','A+','')
,('Papua New Guinea','PG','A','','')
,('Philippines','PH','A','','A*')
,('Pakistan','PK','A','','')
,('Paraguay','PY','A','','')
,('Serbia','RS','A','','')
,('Rwanda','RW','A','A+','')
,('Solomon Islands','SB','A','A+','')
,('Sierra Leone','SL','A','A+','')
,('Senegal','SN','A','A+','')
,('Somalia','SO','A','A+','')
,('Suriname','SR','A','','')
,('South Sudan','SS','A','A+','')
,('Sao Tome and Principe','ST','A','','')
,('Swaziland','SZ','A','','')
,('Chad','TD','A','A+','')
,('Togo','TG','A','A+','')
,('Thailand','TH','A','','A*')
,('Timor-Leste','TL','A','A+','')
,('Tunisia','TN','A','','')
,('Tonga','TO','A','','')
,('Turkey','TR','A','','A*')
,('Tuvalu','TV','A','A+','')
,('Tanzania','TZ','A','A+','')
,('Ukraine','UA','A','','')
,('Uganda','UG','A','A+','')
,('Uzbekistan','UZ','A','','')
,('St. Vincent and the Grenadines','VC','A','','')
,('Vanuatu','VU','A','A+','')
,('Samoa','WS','A','A+','')
,('Kosovo','XK','A','','')
,('Republic of Yemen','YE','A','A+','')
,('South Africa','ZA','D','','')
,('Zambia','ZM','A','A+','')
,('Zimbabwe','ZW','A','','');
END -- FTA Temp Table into first temp table
WITH CTE_1 as (
SELECT
PROD_ID
,D.[solicitation_type]
,D.SUPPLIER_ID
,D.SOLICITATION_ID
FROM
[SOLICIT].[gtm_doc] D
Inner join [SOLICIT].[gtm_doc_prod] P
On
D.[SOLICITATION_ID] = P.[SOLICITATION_ID]
AND D.[SUB_ORG] = P.[SUB_ORG]
AND D.[SOLICITATION_TYPE] = P.[SOLICITATION_TYPE]
INNER JOIN
TSC.EMS_EDI E
ON P.[PROD_ID] = E.edi_ref_num
AND D.SUPPLIER_id = substring(sys_varchar_3,116,5)
Where
D.[trade_program] = 'GSP'
AND D.[solicitation_type] = 'REQUEST'
AND Datepart(year, [blanket_from_date]) = '2018'
AND D.sub_org = 'FORD'
)
, CTE_2 as (
SELECT
PROD_ID
,D.[solicitation_type]
,D.SUPPLIER_ID
,D.SOLICITATION_ID
FROM
[SOLICIT].[gtm_doc] D
Inner join [SOLICIT].[gtm_doc_prod] P
On
D.[SOLICITATION_ID] = P.[SOLICITATION_ID]
AND D.[SUB_ORG] = P.[SUB_ORG]
AND D.[SOLICITATION_TYPE] = P.[SOLICITATION_TYPE]
INNER JOIN
TSC.EMS_EDI E
ON P.[PROD_ID] = E.edi_ref_num
AND D.SUPPLIER_id = substring(sys_varchar_3,116,5)
Where
D.[trade_program] = 'GSP'
AND D.[solicitation_type] = 'RESPONSE'
AND Datepart(year, [blanket_from_date]) = '2018'
AND D.sub_org = 'FORD'
)
,CTE_3 as (
SELECT TOP 1
D.[solicitation_id]
,P.PROD_ID
,D.SUPPLIER_ID
FROM
[SOLICIT].[gtm_doc] D
Inner join [SOLICIT].[gtm_doc_prod] P
On
D.[SOLICITATION_ID] = P.[SOLICITATION_ID]
AND D.[SUB_ORG] = P.[SUB_ORG]
AND D.[SOLICITATION_TYPE] = P.[SOLICITATION_TYPE]
INNER JOIN
TSC.EMS_EDI E
ON P.[PROD_ID] = E.edi_ref_num
AND D.SUPPLIER_id = substring(sys_varchar_3,116,5)
WHERE
D.[trade_program] = 'GSP'
AND D.[solicitation_type] = 'REQUEST'
AND DATEPART(YEAR,[BLANKET_FROM_DATE]) = '2018'
AND D.SUB_ORG = 'FORD'
order by p.[SOLICITATION_ID] desc
)
--edi_ref_num prodid, substring(sys_varchar_3,116,5)
Select
'GSP'
,substring(sys_varchar_3,116,5) as Supplier
,GetDate()+21 as RequestDueDate
,'' as RequestSupplierItemkey
,edi_ref_num as RequestClientItemKey
,P.DESC_EXPAND as Description
,CAST(SUBSTRING(substring(sys_varchar_3,172,15), PATINDEX('%[^0 ]%', substring(sys_varchar_3,172,15) + ' '), LEN(substring(sys_varchar_3,172,15))) as float)/1000000 as [Unverified Unit Price]
,min(cast(sent_date as date)) min_sentdate
,max(cast(sent_date as date)) max_sentdate
,sent_by as Site
,substring(sys_varchar_3,116,5) as ship_from
,substring(sys_varchar_3,123,5) as ship_to
,substring(sys_varchar_3,121,2) as ship_ctry
,CONCAT(T.GSPA,',', T.GSPASTAR,',',T.GSPLDBC) as FTA
,CTE_1.SOLICITATION_TYPE as Request
,CTE_1.[solicitation_id] as Request_Sol_ID
,CTE_2.SOLICITATION_TYPE as Response
,CTE_2.SOLICITATION_ID as Response_ID
,C.CLASS_VALUE_1 as HTS
,S.[STATUS]
,S.[SUPPLIER_NAME]
,P.Prod_ID
from
TSC.EMS_EDI E
LEFT JOIN #TEMP1 T
ON T.ISO = substring(sys_varchar_3,121,2)
LEFT JOIN CTE_1
ON E.edi_ref_num = CTE_1.PROD_ID
AND substring(sys_varchar_3,116,5) = CTE_1.SUPPLIER_ID
LEFT JOIN CTE_2
ON edi_ref_num = CTE_2.[prod_id]
AND substring(sys_varchar_3,116,5) = CTE_2.SUPPLIER_ID
LEFT JOIN [TSC].[EMS_TVC_PROD_CLASS] C
ON E.edi_ref_num = 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'
LEFT JOIN [SOLICIT].[GTM_SUPPLIER] S
ON substring(sys_varchar_3,116,5) = S.[SUPPLIER_ID]
LEFT JOIN [TSC].[EMS_TVC_PROD] P
ON edi_ref_num = P.PROD_ID and p.SUB_ORG='Ford'
WHERE
edi_type = 'BCS_MQ_RECEIVED'
and edi_sub_type like 'JCST381U'
and sent_date > getdate( ) - 20
AND T.CTRY IS NOT NULL
GROUP BY
edi_ref_num , sent_by,
substring(sys_varchar_3,116,5) ,
substring(sys_varchar_3,123,5) ,
substring(sys_varchar_3,121,2) ,
substring(sys_varchar_3,172,15) ,
edi_sub_type,
T.Ctry,
T.GSPA,
T.GSPLDBC,
T.GSPASTAR,
T.ISO,
CTE_1.SOLICITATION_TYPE
,CTE_1.SOLICITATION_ID
,CTE_2.SOLICITATION_TYPE
,CTE_2.SOLICITATION_ID
,C.CLASS_VALUE_1
,S.[STATUS]
,S.[SUPPLIER_NAME]
,DESC_EXPAND
,P.Prod_ID
,edi_ref_num
END
March 14, 2018 at 10:31 am
I tried that , but now i get a : There is already an object named '#TEMP1' in the database. error
Do i need an END at the VERY End . . .
March 14, 2018 at 10:36 am
jeffshelix - Wednesday, March 14, 2018 10:31 AMI tried that , but now i get a : There is already an object named '#TEMP1' in the database. errorDo i need an END at the VERY End . . .
Look at your code, the DROP for the temp table is in the IF part of your code not the ELSE part. You should probably move that little piece outside the IF statement.
And yes, you need the END at the END as it marks the end of BEGIN END block for the ELSE.
March 14, 2018 at 10:42 am
Since you're using the same temp table whichever branch of the IF you go along, why not just create it at the beginning, outside the IF logic?
You need an END to correspond with each and every BEGIN. If you indent them, you'll find it a lot easier to spot anomalies.
IF < something is true >
BEGIN
-- Do something here
BEGIN
-- You can nest BEGINs if necessary
END
END
ELSE IF < something else is true >
BEGIN
-- Do something different here
END
John
March 14, 2018 at 10:43 am
Solved my issue . . . . in the second SQL, i changed table to #TEMP2
thanks
March 14, 2018 at 10:46 am
jeffshelix - Wednesday, March 14, 2018 10:43 AMSolved my issue . . . . in the second SQL, i changed table to #TEMP2thanks
Did you also add a DROP TABLE if it exists?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply