January 5, 2011 at 3:14 am
Hello Friends,
I am trying to modify a query based on changes in the functional specs. Would it be possible for you to help me with this ?
In a table I have a list of countries and each country has been assigned a two-character code and each code is unique. Only in the case of France(FR), there are two versions- FRN and FRS.
As the resultset is displayed based on only the first two characters, I am getting duplicate data for the same
What I wish to accomplish is only display data for either FRN or FRS. If the country code begins with FR(France) in the POL_COUNTRY_CD or POD_COUNTRY_CD column in NCV_BL_DHL_TEMP table then join to table, MG_LOCATION to identify continent. If continent is equal to 'EUR' then lookup 'FRN' in a table called DHL_TRADE_ASSIGNMENT. If continent is equal to 'MED' then lookup 'FRS'.
I have already created a query and need to fit in the above scenario in the same. Please find attached the query.
Please find the the table structure for the 3 tables that I am using below -
First one is the DHL_TRADE_ASSIGNMENT table-
-----------------------------------------------------------------
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DHL_TRADE_ASSIGNMENT](
[COUNTRY_CD] [nvarchar](20) NOT NULL,
[COUNTRY_DSC] [nvarchar](50) NULL,
[REGION_TRADE] [nvarchar](50) NULL,
[SUB_REGION] [nvarchar](50) NULL,
UNIQUE NONCLUSTERED
(
[COUNTRY_CD] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
-----------------------------------------------------------------------
Sample data for DHL_TRADE_ASSIGNMENT-
-----------------------------------------------------------------------
insert into DHL_TRADE_ASSIGNMENT
values('FRS', 'France South', 'EURO MED', 'EURO MED');
insert into DHL_TRADE_ASSIGNMENT
values('FRN', 'France North', 'EURO NC', 'EURO NC');
insert into DHL_TRADE_ASSIGNMENT
values('ES', 'SPAIN', 'EURO MED', 'EURO MED');
insert into DHL_TRADE_ASSIGNMENT
values('AT', 'AUSTRIA', 'EURO NC', 'EURO NC');
insert into DHL_TRADE_ASSIGNMENT
values('AU', 'Australia', 'SPAC', 'SPAC');
insert into DHL_TRADE_ASSIGNMENT
values('IN', 'India', 'ASPA', 'ASPA');
insert into DHL_TRADE_ASSIGNMENT
values('RO', 'Romania', 'BLACK SEA', 'BLACK SEA');
insert into DHL_TRADE_ASSIGNMENT
values('CA', 'Canada', 'AMNO', 'AMNO');
insert into DHL_TRADE_ASSIGNMENT
values('AG', 'Antigua', 'AMLA', 'NCSA');
insert into DHL_TRADE_ASSIGNMENT
values('IS', 'Iceland', 'EURO NC', 'OTHERS EURO');
insert into DHL_TRADE_ASSIGNMENT
values('EC', 'EC', 'AMLA', 'WCSA');
insert into DHL_TRADE_ASSIGNMENT
values('CN', 'China', 'ASPA', 'ASPA');
insert into DHL_TRADE_ASSIGNMENT
values('GB', 'Great Britain', 'EURO NC', 'EURO NC');
---------------------------------------------------------------------
2nd table- NCV_BL_DHL_TEMP
-----------------------------------------------------------------------
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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,
PRIMARY KEY CLUSTERED
(
[BL_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
-----------------------------------------------------------------------------------------
Sample data for NCV_BL_DHL_TEMP-
-----------------------------------------------------------------------------------------
insert into NCV_BL_DHL_TEMP
values('6525833', 'MAA012330', 'SAIMA', 'IN', 'GB', 'HCMB', '0083W', 'W', '03-05-2010', '2');
insert into NCV_BL_DHL_TEMP
values('7102907', 'SH8634919', 'AL_FILTER', 'GB', 'IN', 'XYKOU', '0058W', 'W', '06-05-2010', '2');
insert into NCV_BL_DHL_TEMP
values('6117626', 'GLA007219', 'HANKYU', 'AU', 'AT', 'HANB', '01E', 'E', '02-02-2010', '1');
insert into NCV_BL_DHL_TEMP
values('5772271', 'BLR009972', 'TTP_TECHNOLOGIE', 'AT', 'AU', 'HJCH', '0036W', 'W', '04-05-2010', '1');
insert into NCV_BL_DHL_TEMP
values('6106668', 'LEH901114', 'SAEME', 'FR', 'HK', 'SVB', '648E', 'E', '02-06-2009', '2');
insert into NCV_BL_DHL_TEMP
values('6137996', 'FXT128719', 'FELIANCE_FIBRES', 'TR', 'ES', 'CNTSNG', '258E', 'E', '01-09-2010', '4');
insert into NCV_BL_DHL_TEMP
values('6168340', 'ANR490955', 'DHL_DANZAS', 'BR', 'CA', 'CSGNZU', '006E', 'E', '02-08-2010', '2');
insert into NCV_BL_DHL_TEMP
values('6168346', 'ANR440555', 'DHL_DANZAS', 'CA', 'BR', 'CSGWZU', '006E', 'E', '02-01-2008', '2');
insert into NCV_BL_DHL_TEMP
values('6581123', 'HK1016941', 'OKI', 'CN', 'GB', 'YPULT', '03W', 'W', '02-02-2007', '2');
insert into NCV_BL_DHL_TEMP
values('6581127', 'HK5010911', 'KOK', 'AG', 'IS', 'YMULT', '03W', 'W', '04-06-2008', '2');
insert into NCV_BL_DHL_TEMP
values('6581123', 'HD1086941', 'KPP', 'IS', 'AG', 'YKULT', '03W', 'W', '02-05-2009', '2');
-----------------------------------------------------
3rd table- MG_LOCATION
-----------------------------------------------------
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MG_LOCATION](
[LOCATION_CD] [varchar](5) NOT NULL,
[LOCATION_DSC] [varchar](35) NULL,
[STATE_CD] [varchar](3) NULL,
[COUNTRY_CD] [varchar](2) NOT NULL,
[CONTINENT_CD] [varchar](3) NULL,
CONSTRAINT [PK_MG_LOCATION] PRIMARY KEY CLUSTERED
(
[LOCATION_CD] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
--------------------------------------------------------------------
Sample data for MG_LOCATION
-----------------------------------------
insert into MG_LOCATION
values('FRTAR', 'TARNOS', 'B', 'FR', 'MED');
insert into MG_LOCATION
values('FRTCC', 'TOCANE-SAINT-APRE', 'B', 'FR', 'EUR');
insert into MG_LOCATION
values('FRTCN', 'TUCHAN', 'C', 'FR', 'MED');
insert into MG_LOCATION
values('GBCNE', 'CALNE', 'D', 'GB', 'EUR');
insert into MG_LOCATION
values('IT2QW', 'CASTEL CONDINO', 'E', 'IT', 'EUR');
------------------------------------------------------------
I hope the above information would help. Any help or starting point would be deeply appreciated.
Thanks a lot in advance !!!
Regards,
Paul
January 5, 2011 at 3:35 am
What I wish to accomplish is only display data for either FRN or FRS. If the country code begins with FR(France) in the POL_COUNTRY_CD or POD_COUNTRY_CD column in NCV_BL_DHL_TEMP table then join to table, MG_LOCATION to identify continent. If continent is equal to 'EUR' then lookup 'FRN' in a table called DHL_TRADE_ASSIGNMENT. If continent is equal to 'MED' then lookup 'FRS'.
Thanks for the DDL and sample data, that will help. However, I want to make sure of your business requirement. You're essentially looking for something like this? Please note the undefined's, I'm not sure what these links are supposed to be and I didn't want to dig through the data to end up with wrong assumptions anyway. 🙂
SELECT
*
FROM
NCV_BL_DHL_TEMP AS n
JOIN
MG_Location AS m
ON--<Undefined Join>
JOIN
DHL_TRADE_ASSIGNMENT AS dta
ON( m.continent = 'EUR' AND dta./*<undefinedfield>*/ = 'FRN')
OR ( m.continent = 'MED' AND dta./*<undefinedfield>*/ = 'FRS')
WHERE
n.Pol_Country_CD = 'FR'
OR n.POD_Country_CD = 'FR'
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 5, 2011 at 4:01 am
Hello Craig,
Thanks a lot for your quick answer !! Your code is absolutely correct and I am not getting any duplicates now.
The problem I am now have is to combine your code with my query. The main task is to assign a tradelane for each BL_ID as shown in the query. Could you please give me some idea on how to go about it ? Please find my sample query attached.
Thanks again !
Paul
January 6, 2011 at 12:35 am
On this portion:
from NCV_BL_DHL_TEMP a
INNER JOIN
DHL_TRADE_ASSIGNMENT b
on a.POL_COUNTRY_CD = ltrim(rtrim(substring(b.COUNTRY_CD,1,2)))
INNER JOIN DHL_TRADE_ASSIGNMENT c
on a.POD_COUNTRY_CD = ltrim(rtrim(substring(c.COUNTRY_CD,1,2)))
You'll need your join code to handle the AND/OR switching you'll need to do.
Please be advised: This is going to run horribly if you have a lot of data.
from NCV_BL_DHL_TEMP a
INNER JOIN
DHL_TRADE_ASSIGNMENT b
on a.POL_COUNTRY_CD = ltrim(rtrim(substring(b.COUNTRY_CD,1,2)))
INNER JOIN DHL_TRADE_ASSIGNMENT c
on
(a.POD_Country_CD = 'FR'
AND ( ( m.continent = 'EUR' AND ltrim(rtrim(substring(c.COUNTRY_CD,1,3))) = 'FRN')
OR ( m.continent = 'MED' AND ltrim(rtrim(substring(c.COUNTRY_CD,1,3))) = 'FRS') )
)
OR (a.Pod_Country_CD <> 'FR' AND
a.POD_COUNTRY_CD = ltrim(rtrim(substring(c.COUNTRY_CD,1,2)))
)
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 6, 2011 at 2:49 am
Better to write a CTE and finalize the values need to compared and then make the join with simple condition.
January 6, 2011 at 3:34 am
Hi Craig,
Thanks a lot again for your solution. However, I am getting duplicate values now not due to your code but due to the other piece of code thats already there in the query I have created.
Is it possible to include your code in the case statement itself before the actual joins ?
Please find my query attached. In this query, there are some case statements and based upon the condition, the data for FR is displayed. I dont know if I have to re-write the query to resolve the issue of duplicate values.
Kind Regards,
Paul
January 6, 2011 at 4:06 am
Hi Harinerella,
Thanks for your reply. However, I dont know what you mean by CTE ?
January 6, 2011 at 11:09 am
pwalter83 (1/6/2011)
Hi Harinerella,Thanks for your reply. However, I dont know what you mean by CTE ?
CTE is a common table expression. It's a pretty way to write a subquery. There's some misunderstanding about it that it preprocesses, which it doesn't. It's the same as if you took the SELECT FROM.. query and dropped it in parenthetically as a derived table.
The way you would try to optimize is to drop the result to a table temp/var and use that in the query.
I'll try to take a look at your new query tonight when I get home if I can wrangle some time out of life.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 10, 2011 at 2:13 am
Hi Craig,
Thanks for your reply. Is it possible for you to give some hint into this ? I am struggling with this for a long time now.
Kind Regards,
Paul
January 10, 2011 at 8:04 am
harinerella (1/6/2011)
Better to write a CTE and finalize the values need to compared and then make the join with simple condition.
Cool! Let's see some code. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
January 10, 2011 at 9:19 am
Hi Jeff,
Thanks a lot for your reply. Please pardon me for the lengthy message below-
What I am trying to do is trying to modify a query based on changes in the functional specs.
In a table I have a list of countries and each country has been assigned a two-character code and each code is unique. Only in the case of France(FR), there are two versions- FRN and FRS.
As the resultset is displayed based on only the first two characters, I am getting duplicate data for the same.
What I wish to accomplish is only display data for either FRN or FRS. If the country code begins with FR(France) in the POL_COUNTRY_CD or POD_COUNTRY_CD column in NCV_BL_DHL_TEMP table then join to table, MG_LOCATION to identify continent. If continent is equal to 'EUR' then lookup 'FRN' in a table called DHL_TRADE_ASSIGNMENT. If continent is equal to 'MED' then lookup 'FRS'.
I have already created a query and need to fit in the above scenario in the same. Please find attached the Sample Query.
Please find the the table structure for the 3 tables that I am using below in the sample query and the fourth one which is MG_LOCATION and has to be joined to get the desired FR functionality:
First one is the DHL_TRADE_ASSIGNMENT table-
-----------------------------------------------------------------
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DHL_TRADE_ASSIGNMENT](
[COUNTRY_CD] [nvarchar](20) NOT NULL,
[COUNTRY_DSC] [nvarchar](50) NULL,
[REGION_TRADE] [nvarchar](50) NULL,
[SUB_REGION] [nvarchar](50) NULL,
UNIQUE NONCLUSTERED
(
[COUNTRY_CD] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
-----------------------------------------------------------------------
Sample data for DHL_TRADE_ASSIGNMENT-
-----------------------------------------------------------------------
insert into DHL_TRADE_ASSIGNMENT
values('FRS', 'France South', 'EURO MED', 'EURO MED');
insert into DHL_TRADE_ASSIGNMENT
values('FRN', 'France North', 'EURO NC', 'EURO NC');
insert into DHL_TRADE_ASSIGNMENT
values('ES', 'SPAIN', 'EURO MED', 'EURO MED');
insert into DHL_TRADE_ASSIGNMENT
values('AT', 'AUSTRIA', 'EURO NC', 'EURO NC');
insert into DHL_TRADE_ASSIGNMENT
values('AU', 'Australia', 'SPAC', 'SPAC');
insert into DHL_TRADE_ASSIGNMENT
values('IN', 'India', 'ASPA', 'ASPA');
insert into DHL_TRADE_ASSIGNMENT
values('RO', 'Romania', 'BLACK SEA', 'BLACK SEA');
insert into DHL_TRADE_ASSIGNMENT
values('CA', 'Canada', 'AMNO', 'AMNO');
insert into DHL_TRADE_ASSIGNMENT
values('AG', 'Antigua', 'AMLA', 'NCSA');
insert into DHL_TRADE_ASSIGNMENT
values('IS', 'Iceland', 'EURO NC', 'OTHERS EURO');
insert into DHL_TRADE_ASSIGNMENT
values('EC', 'EC', 'AMLA', 'WCSA');
insert into DHL_TRADE_ASSIGNMENT
values('CN', 'China', 'ASPA', 'ASPA');
insert into DHL_TRADE_ASSIGNMENT
values('GB', 'Great Britain', 'EURO NC', 'EURO NC');
---------------------------------------------------------------------
2nd table- NCV_BL_DHL_TEMP
-----------------------------------------------------------------------
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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,
PRIMARY KEY CLUSTERED
(
[BL_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
-----------------------------------------------------------------------------------------
Sample data for NCV_BL_DHL_TEMP-
-----------------------------------------------------------------------------------------
insert into NCV_BL_DHL_TEMP
values('10286790', 'MAA012330', 'SAIMA', 'IN', 'GB', 'HCMB', '0083W', 'W', '03-05-2010', '2');
insert into NCV_BL_DHL_TEMP
values('10296280', 'SH8634919', 'AL_FILTER', 'GB', 'IN', 'XYKOU', '0058W', 'W', '06-05-2010', '2');
insert into NCV_BL_DHL_TEMP
values('10320493', 'GLA007219', 'HANKYU', 'AU', 'AT', 'HANB', '01E', 'E', '02-02-2010', '1');
insert into NCV_BL_DHL_TEMP
values('10442279', 'BLR009972', 'TTP_TECHNOLOGIE', 'AT', 'AU', 'HJCH', '0036W', 'W', '04-05-2010', '1');
insert into NCV_BL_DHL_TEMP
values('10443663', 'LEH901114', 'SAEME', 'FR', 'HK', 'SVB', '648E', 'E', '02-06-2009', '2');
insert into NCV_BL_DHL_TEMP
values('10735227', 'FXT128719', 'FELIANCE_FIBRES', 'TR', 'ES', 'CNTSNG', '258E', 'E', '01-09-2010', '4');
insert into NCV_BL_DHL_TEMP
values('10739501', 'ANR490955', 'DHL_DANZAS', 'BR', 'CA', 'CSGNZU', '006E', 'E', '02-08-2010', '2');
insert into NCV_BL_DHL_TEMP
values('10874599', 'ANR440555', 'DHL_DANZAS', 'CA', 'BR', 'CSGWZU', '006E', 'E', '02-01-2008', '2');
insert into NCV_BL_DHL_TEMP
values('11272481', 'HK1016941', 'OKI', 'CN', 'GB', 'YPULT', '03W', 'W', '02-02-2007', '2');
insert into NCV_BL_DHL_TEMP
values('11852019', 'HK5010911', 'KOK', 'AG', 'IS', 'YMULT', '03W', 'W', '04-06-2008', '2');
insert into NCV_BL_DHL_TEMP
values('11852452', 'HD1086941', 'KPP', 'IS', 'AG', 'YKULT', '03W', 'W', '02-05-2009', '2');
--------------------------------------------------------------------------------------
3rd table- NCV_FREIGHT_DHL_TEMP
----------------------------------------------------------------------
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[NCV_FREIGHT_DHL_TEMP](
[BL_ID] [decimal](10, 0) NOT NULL,
[BL_FREIGHT_ID] [decimal](10, 0) NOT NULL,
[CHARGE_CD] [nvarchar](3) NULL,
[FRT_USD] [decimal](12, 3) NULL,
PRIMARY KEY CLUSTERED
(
[BL_FREIGHT_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
-----------------------------------------------------------------------------------------
Sample data for NCV_FREIGHT_DHL_TEMP-
-----------------------------------------------------------------------------------------
insert into NCV_FREIGHT_DHL_TEMP
values('10443663', '41640712', 'OFT', '1200.000');
insert into NCV_FREIGHT_DHL_TEMP
values('10735227', '42906574', 'OFT', '665.000');
insert into NCV_FREIGHT_DHL_TEMP
values('10874599', '42913602', 'DOC', '21.730');
insert into NCV_FREIGHT_DHL_TEMP
values('10739501', '42906575', 'PRC', '66.637');
insert into NCV_FREIGHT_DHL_TEMP
values('11852019', '47961402', 'SZC', '36.000');
-------------------------------------------------------------------------------------
4th table- MG_LOCATION
-----------------------------------------------------
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MG_LOCATION](
[LOCATION_CD] [varchar](5) NOT NULL,
[LOCATION_DSC] [varchar](35) NULL,
[STATE_CD] [varchar](3) NULL,
[COUNTRY_CD] [varchar](2) NOT NULL,
[CONTINENT_CD] [varchar](3) NULL,
CONSTRAINT [PK_MG_LOCATION] PRIMARY KEY CLUSTERED
(
[LOCATION_CD] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
--------------------------------------------------------------------
Sample data for MG_LOCATION
-----------------------------------------
insert into MG_LOCATION
values('FRTAR', 'TARNOS', 'B', 'FR', 'MED');
insert into MG_LOCATION
values('FRTCC', 'TOCANE-SAINT-APRE', 'B', 'FR', 'EUR');
insert into MG_LOCATION
values('FRTCN', 'TUCHAN', 'C', 'FR', 'MED');
insert into MG_LOCATION
values('GBCNE', 'CALNE', 'D', 'GB', 'EUR');
insert into MG_LOCATION
values('IT2QW', 'CASTEL CONDINO', 'E', 'IT', 'EUR');
------------------------------------------------------------
I hope the above information would help. Any help or starting point would be deeply appreciated.
Thanks a lot in advance !!!
Regards,
Paul
January 10, 2011 at 10:03 am
Hi Paul,
Sorry... my previous response was directed to harinerella.
Thanks for the setup code and test data. Unfortunately, I'm on my way to work and won't be able to look at this until tonight.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 10, 2011 at 3:26 pm
Paul,
I'm not sure if I completely understood the logic behind your CASE statements. But to me it looks like you could add a (persisted) computed column to your DHL_TRADE_ASSIGNMENT table that would take care of most of the scenarios. It's all based on COUNTRY_CD, right?
Your complex CASE statement could be replaced with
b.Sub_Region +' to ' + c.REGION_TRADE as TRADELANE
You still might need to include it in a CASE statement to conver specific scenarios like 'EURO MED' = 'EURO MED'.
If there are more than 5 to 10 exceptions I'd probably use a specific exceptions table that would hold the two COUNTRY_CD values and the related TRADELANE values.
I would then reference this table using an outer join based to NCV_BL_DHL_TEMP on POL_COUNTRY_CD and POD_COUNTRY_CD.
The statement then would look like
ISNULL(exceptions.POL_TRADELANE ,b.Sub_Region) +' to ' + ISNULL(exceptions.POD_TRADELANE, c.REGION_TRADE) as TRADELANE
And here's a short example how a computed column could look like:
SELECT
*,
CASE
WHEN COUNTRY_CD ='GB' THEN 'Euro NC/UK'
WHEN COUNTRY_CD IN('US','CA') THEN COUNTRY_CD
WHEN REGION_TRADE = Sub_Region THEN REGION_TRADE
WHEN REGION_TRADE <> Sub_Region THEN Sub_Region
ELSE 'OTHERS'
END as computed_column
FROM DHL_TRADE_ASSIGNMENT
January 10, 2011 at 5:31 pm
Got distracted a while.
Here's part of the problem:
/*
--One FR sample row
insert into NCV_BL_DHL_TEMP
values('10443663', 'LEH901114', 'SAEME', 'FR', 'HK', 'SVB', '648E', 'E', '02-06-2009', '2');
-- ALL MG_Location Sample rows
insert into MG_LOCATION
values('FRTAR', 'TARNOS', 'B', 'FR', 'MED');
insert into MG_LOCATION
values('FRTCC', 'TOCANE-SAINT-APRE', 'B', 'FR', 'EUR');
insert into MG_LOCATION
values('FRTCN', 'TUCHAN', 'C', 'FR', 'MED');
insert into MG_LOCATION
values('GBCNE', 'CALNE', 'D', 'GB', 'EUR');
insert into MG_LOCATION
values('IT2QW', 'CASTEL CONDINO', 'E', 'IT', 'EUR');
*/
Please show me how to connect the row from NCV_BL_DHL_TEMP to the correct row in MG_Location? That's probably the crux of the duplication issue right there. You MUST have a unique way to get to the continent identifier from the source data.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 10, 2011 at 5:55 pm
pwalter83 (1/10/2011)
Hi Jeff,Thanks a lot for your reply. Please pardon me for the lengthy message below-
Hi Paul,
I'm a wee bit confused. I built your tables, loaded your data, and commented out the WHERE clause for the 'FR' thing, but I get no data other than OTHERS back and it doesn't change in value. Are you sure this is all working ok?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 39 total)
You must be logged in to reply to this topic. Login to reply