December 20, 2010 at 3:58 am
Hello Friends,
Could you please help me develop a logic for a SQL query in SSIS which goes as under :
I need to assign a value to a column called 'tradelane' for each unique BL_ID in the NCV_BL_DHL_TEMP
table. This column will be part of a newly created table called- DHL_TEMP and table structure is as follows:
CREATE TABLE [dbo].[DHL_TEMP]
(
[BL_ID] [decimal](10, 0) NOT NULL,
[DEPART_ACTUAL_DT] [datetime] NULL,
[TEU] [decimal](10, 0) NULL,
[FRT_USD] [decimal](12, 0) NULL,
[TRADELANE] [nvarchar](50) NULL,
)
The value is to be assigned based on using columns from 2 other tables-
1. NCV_BL_DHL_TEMP
CREATE TABLE [dbo].[NCV_BL_DHL_TEMP]
(
[BL_ID] [decimal](10, 0) NOT NULL,
[BL_NUM] [nvarchar](13) NULL,
[CP_GROUP_CD] [nvarchar](30) NULL,
[POL_COUNTRY_CD] [nvarchar](2) NULL,
[POD_COUNTRY_CD] [nvarchar](2) NULL,
[SAISAN_VESSEL_CD] [nvarchar](6) NULL,
[SAISAN_VOYAGE_CD] [nvarchar](6) NULL,
[SAISAN_LEG_CD] [nvarchar](1) NULL,
[DEPART_ACTUAL_DT] [datetime] NULL,
[TEU] [decimal](10, 0) NULL
)
2. DHL_TRADE_ASSIGNMENT-
CREATE TABLE [dbo].[DHL_TRADE_ASSIGNMENT]
(
[COUNTRY_CD] [nvarchar](2) NOT NULL,
[COUNTRY_DSC] [nvarchar](50) NULL,
[REGION_TRADE] [nvarchar](3) NULL,
[SUB_REGION] [nvarchar](50) NULL
)
The DHL_TRADE_ASSIGNMENT is a static table and will have limited rows. The sample data from the same is as under:
Country Code--Country------Region/Trade-----Sub Region
AO------------Angola-------EMA------------- AFRICA
BI------- -----Burundi-------EMA------------- AFRICA
BJ-------------Benin--------EMA------------- AFRICA
CG------------Congo--------EMA-------------AFRICA
The Tradelane definitions are based on this sample data-
TRADELANE-----------------POL_COUNTRY_CD----------------POD_COUNTRY_CD
Euro NC/UK to ASPA---------Starts with GB-------------Any country where Region/Trade = ASPA
Euro NC/UK to SPAC-------- Starts with GB-------------Any country where Region/Trade = SPAC
Euro NC/UK to US-----------Starts with GB-------------Starts with US
Euro NC/UK to CA-----------Starts with GB-------------Starts with CA
Euro NC/UK to AMLA-------- Starts with GB-------------Any country where Region/Trade = AMLA
Euro NC/UK to EMA ---------Starts with GB-------------Any country where Region/Trade = EMA
This query is part of an SSIS package and I am stuck with this for a long time now. Any little help would be deeply appreciated as it would at least give me a start. Thanks a lot in advance.
Regards.
Paul
December 20, 2010 at 6:43 am
Hi Paul
I read ur topic,what i understand is
You have to assign value to a colum DHL_TEMP.TRADELANE for each NCV_BL_DHL_TEMP.BL_ID
and the condition is
TRADELANE-----------------POL_COUNTRY_CD----------------POD_COUNTRY_CD
Euro NC/UK to ASPA---------Starts with GB-------------Any country where Region/Trade = ASPA
Euro NC/UK to SPAC-------- Starts with GB-------------Any country where Region/Trade = SPAC
for this you need two input
1.NCV_BL_DHL_TEMP.POL_COUNTRY_CD starts with GB
2.DHL_TRADE_ASSIGNMENT.REGION_TRADE by mapping NCV_BL_DHL_TEMP.POD_COUNTRY_CD with
DHL_TRADE_ASSIGNMENT.COUNTRY_CD
If I am right this may help you
1.Use one OleDbSource and write the following Query
select a.BL_ID,a.POL_COUNTRY_CD,a.POD_COUNTRY_CD,b.REGION_TRADE,
TradeLane=
case ltrim(rtrim(substring(a.POL_COUNTRY_CD,1,2) ))
when 'GB' then
case b.REGION_TRADE when 'ASPA' then 'Euro NC/UK to ASPA' end
when 'GB' then
case b.REGION_TRADE when 'SPAC' then 'Euro NC/UK to SPAC' end
end
from NCV_BL_DHL_TEMP a
inner join
DHL_TRADE_ASSIGNMENT b
on a.POD_COUNTRY_CD=b.COUNTRY_CD
2.The OledbSource output will contain the NCV_BL_DHL_TEMP .BL_ID ,Tradelane value .Use one OledbDestination to insert the value in DHL_TEMP table
Incase any query let me know
Thanks and Regards
Rashmi
December 20, 2010 at 7:23 am
Thanks a ton Rashmi...I think you have nailed it...i will test the query and let you know...thanks very much for your patience and the resulting solution...
I am very new to this forum...Do you know how can i mark ur solution as answer to appraise you on this forum ?
Kind Regards,
Paul
December 20, 2010 at 8:03 am
pwalter83 (12/20/2010)
...Do you know how can i mark ur solution as answer to appraise you on this forum ?
...
I'm afraid that such functionality is not available in this forum.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 21, 2010 at 4:47 am
Hi Rashmi,
Thanks a lot for your earlier solution. It resolved the main issue. If you could please help me with one more thing it would be great.
If you remember from yesterday, the tradelane needed to be defined as per the Tradelane definition.
I need to define one tradelane as per below:
TRADELANE-----------POL_COUNTRY_CD------ --------------------POD_COUNTRY_CD
Intra Euro+MED---Any country where Region/Trade= EURO MED---Any country where region/Trade = EMA
I have devised the following code for this but it doesnt seem to work:
case ltrim(rtrim(substring(a.POL_COUNTRY_CD,1,2))) when '%%' then
case b.REGION_TRADE when 'EURO MED' then
case ltrim(rtrim(substring(a.POD_COUNTRY_CD,1,2) )) when '%%' then
case b.REGION_TRADE when 'EMA' then 'Intra Euro+MED'
end
end
end
Is it possible for you to look into it ?
Thanks again with regards,
Paul
December 22, 2010 at 2:08 am
Hi paul
To get your desired result You need to change in your previous query
.It should be
select a.BL_ID,a.POL_COUNTRY_CD,a.POD_COUNTRY_CD, b.REGION_TRADE as REGION_TRADE_POLCOUNTRYCD,c.REGION_TRADE as REGION_TRADE_PODCOUNTRYCD,
Tradelane=
case ltrim(rtrim(substring(a.POL_COUNTRY_CD,1,2) ))
when 'GB' then
case b.REGION_TRADE when 'ASPA' then 'Euro NC/UK to ASPA' end
when 'GB' then
case b.REGION_TRADE when 'SPAC' then 'Euro NC/UK to SPAC' end
else
case b.REGION_TRADE when 'EURO MED' then
case c.REGION_TRADE when 'EMA' then 'Intra Euro+MED' end
end
from NCV_BL_DHL_TEMP a
inner join DHL_TRADE_ASSIGNMENT b on a.POL_COUNTRY_CD=b.COUNTRY_CD
inner join DHL_TRADE_ASSIGNMENT c on a.POD_COUNTRY_CD=c.COUNTRY_CD
Hope this will solve your problem
Incase any query let me know
Regards
Rashmi
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply