January 15, 2011 at 2:33 pm
LutzM (1/15/2011)
The sample data can be found here (it's the Edit: 11th post of this thread).
Heh... I know that, Lutz. 🙂 I was suggesting that the OP repost the data with a continent notation somewhere in it besides just MG_Location because there's nothing to join by continent with in any of the other tables. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
January 17, 2011 at 3:00 am
Hi Jeff,
Thanks for your reply and sorry about not providing the updated tables and sample data. Please find the same here:
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('TW', 'TAIWAN', 'ASPA', 'ASPA');
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,
[POL_LOCATION_CD] [nvarchar](5) NULL,
[POD_COUNTRY_CD] [nvarchar](2) NULL,
[POD_LOCATION_CD] [nvarchar](5) NULL,
[SAISAN_VESSEL_CD] [nvarchar](6) NULL,
[SAISAN_VOYAGE_CD] [nvarchar](6) NULL,
[SAISAN_LEG_CD] [nvarchar](1) NULL,
[SAISAN_MONTH] [nvarchar](6) 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('10426400', 'LEH075243', 'DHL_DANZAS', 'FR', 'FRLEH', 'US', 'USELZ', 'IORNT', '020W', 'W', '200801', '2');
insert into NCV_BL_DHL_TEMP
values('10432082', 'LEH075143', 'DHL_DANZAS', 'FR', 'FRLEH', 'HK', 'HKHKG', 'HKGB', '0002E', 'E', '200802', '1');
insert into NCV_BL_DHL_TEMP
values('10432674', 'SZN6455000', 'DHL_DANZAS', 'CN', 'CNYTN', 'FR', 'FRLEH', 'HMNB', '18W', 'W', '200803', '1');
insert into NCV_BL_DHL_TEMP
values('10441977', 'FXT179348', 'DHL_DANZAS', 'GB', 'GBFXT', 'CN', 'CNSHA', 'COCN', '011E', 'E', '200804', '2');
insert into NCV_BL_DHL_TEMP
values('10636811', 'LEH076145', 'DHL_DANZAS', 'FR', 'FRFOS', 'TH', 'THLCH', 'MRCH', '058E', 'E', '200801', '4');
insert into NCV_BL_DHL_TEMP
values('11504960', 'LEH904173', 'DHL_DANZAS', 'FR', 'FRFOS', 'PH', 'PHMNS', 'MGRT', '064E', 'E', '200808', '1');
insert into NCV_BL_DHL_TEMP
values('10465280', 'TW2042218', 'DHL_DANZAS', 'TW', 'TWKHH', 'DE', 'DEHAM', 'YMUPW', '074E', 'E', '200810', '8');
--------------------------------------------------------------------------------------
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('10426400', '41554816', 'OFT', '1200.000');
insert into NCV_FREIGHT_DHL_TEMP
values('10426400', '42906574', 'BAF', '665.000');
insert into NCV_FREIGHT_DHL_TEMP
values('10432082', '41578251', 'DOC', '21.730');
insert into NCV_FREIGHT_DHL_TEMP
values('10432082', '42906775', 'PRC', '66.637');
insert into NCV_FREIGHT_DHL_TEMP
values('10432674', '45796142', 'SZC', '36.000');
insert into NCV_FREIGHT_DHL_TEMP
values('10432674', '46751402', 'BNC', '676.000');
insert into NCV_FREIGHT_DHL_TEMP
values('10432674', '40941402', 'RAC', '875.000');
insert into NCV_FREIGHT_DHL_TEMP
values('10441977', '48441437', 'YUC', '51.000');
insert into NCV_FREIGHT_DHL_TEMP
values('10441977', '49641437', 'RLC', '552.000');
insert into NCV_FREIGHT_DHL_TEMP
values('10465280', '44781434', 'LPD', '82.300');
insert into NCV_FREIGHT_DHL_TEMP
values('10465280', '44428334', 'SWQ', '712.114');
insert into NCV_FREIGHT_DHL_TEMP
values('10636811', '41257934', 'HYQ', '257.914');
insert into NCV_FREIGHT_DHL_TEMP
values('10636811', '23547892', 'SIY', '587.000');
insert into NCV_FREIGHT_DHL_TEMP
values('11504960', '52478992', 'MHS', '83.700');
insert into NCV_FREIGHT_DHL_TEMP
values('11504960', '63547814', 'YRS', '54.210');
-------------------------------------------------------------------------------------
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('CNYTN', 'YANTIAN', 'GD', 'CN', 'ASA');
insert into MG_LOCATION
values('FRFOS', 'FOS SUR MER', 'FS', 'FR', 'MED');
insert into MG_LOCATION
values('FRLEH', 'LE HAVRE', 'FC', 'FR', 'EUR');
insert into MG_LOCATION
values('GBFXT', 'FELIXSTOWE', 'GD', 'GB', 'EUR');
insert into MG_LOCATION
values('TWKHH', 'KAOHSIUNG', 'TE', 'TW', 'ASA');
------------------------------------------------------------
Also, please find the sample query attached. Hope this would help you.
Kind Regards,
Paul
January 17, 2011 at 9:22 am
Is there any option to change the values in column COUNTRY_CD, table MG_LOCATION, from FR to FRS rsp. FRN? Then you could simply join on COUNTRY_CD and LOCATION_CD.
If you can't change the values in that column you might be able to add another column CHAR(1) with either N,S or blank.
Other than that I can't see any way to decide if either FRS or FRN should be used.
January 17, 2011 at 1:31 pm
pwalter83 (1/17/2011)
Hi Jeff,Thanks for your reply and sorry about not providing the updated tables and sample data. Please find the same here:
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('TW', 'TAIWAN', 'ASPA', 'ASPA');
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,
[POL_LOCATION_CD] [nvarchar](5) 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('10426400', 'LEH075243', 'DHL_DANZAS', 'FR', 'FRLEH', 'US', 'USELZ', 'IORNT', '020W', 'W', '03-05-2010', '2');
insert into NCV_BL_DHL_TEMP
values('10432082', 'LEH075143', 'DHL_DANZAS', 'FR', 'FRLEH', 'HK', 'HKHKG', 'HKGB', '0002E', 'E', '06-05-2010', '1');
insert into NCV_BL_DHL_TEMP
values('10432674', 'SZN6455000', 'DHL_DANZAS', 'CN', 'CNYTN', 'FR', 'FRLEH', 'HMNB', '18W', 'W', '06-05-2010', '1');
insert into NCV_BL_DHL_TEMP
values('10441977', 'FXT179348', 'DHL_DANZAS', 'GB', 'GBFXT', 'CN', 'CNSHA', 'COCN', '011E', 'E', '06-05-2010', '2');
insert into NCV_BL_DHL_TEMP
values('10636811', 'LEH076145', 'DHL_DANZAS', 'FR', 'FRFOS', 'TH', 'THLCH', 'MRCH', '058E', 'E', '06-05-2010', '4');
insert into NCV_BL_DHL_TEMP
values('11504960', 'LEH904173', 'DHL_DANZAS', 'FR', 'FRFOS', 'PH', 'PHMNS', 'MGRT', '064E', 'E', '06-05-2010', '1');
insert into NCV_BL_DHL_TEMP
values('10465280', 'TW2042218', 'DHL_DANZAS', 'TW', 'TWKHH', 'DE', 'DEHAM', 'YMUPW', '074E', 'E', '06-05-2010', '8');
--------------------------------------------------------------------------------------
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('10426400', '41554816', 'OFT', '1200.000');
insert into NCV_FREIGHT_DHL_TEMP
values('10426400', '42906574', 'BAF', '665.000');
insert into NCV_FREIGHT_DHL_TEMP
values('10432082', '41578251', 'DOC', '21.730');
insert into NCV_FREIGHT_DHL_TEMP
values('10432082', '42906775', 'PRC', '66.637');
insert into NCV_FREIGHT_DHL_TEMP
values('10432674', '45796142', 'SZC', '36.000');
insert into NCV_FREIGHT_DHL_TEMP
values('10432674', '46751402', 'BNC', '676.000');
insert into NCV_FREIGHT_DHL_TEMP
values('10432674', '40941402', 'RAC', '875.000');
insert into NCV_FREIGHT_DHL_TEMP
values('10441977', '48441437', 'YUC', '51.000');
insert into NCV_FREIGHT_DHL_TEMP
values('10441977', '49641437', 'RLC', '552.000');
insert into NCV_FREIGHT_DHL_TEMP
values('10465280', '44781434', 'LPD', '82.300');
insert into NCV_FREIGHT_DHL_TEMP
values('10465280', '44428334', 'SWQ', '712.114');
insert into NCV_FREIGHT_DHL_TEMP
values('10636811', '41257934', 'HYQ', '257.914');
insert into NCV_FREIGHT_DHL_TEMP
values('10636811', '23547892', 'SIY', '587.000');
insert into NCV_FREIGHT_DHL_TEMP
values('11504960', '52478992', 'MHS', '83.700');
insert into NCV_FREIGHT_DHL_TEMP
values('11504960', '63547814', 'YRS', '54.210');
-------------------------------------------------------------------------------------
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('CNYTN', 'YANTIAN', 'GD', 'CN', 'ASA');
insert into MG_LOCATION
values('FRFOS', 'FOS SUR MER', 'FS', 'FR', 'MED');
insert into MG_LOCATION
values('FRLEH', 'LE HAVRE', 'FC', 'FR', 'EUR');
insert into MG_LOCATION
values('GBFXT', 'FELIXSTOWE', 'GD', 'GB', 'EUR');
insert into MG_LOCATION
values('TWKHH', 'KAOHSIUNG', 'TE', 'TW', 'ASA');
------------------------------------------------------------
Also, please find the sample query attached. Hope this would help you.
Kind Regards,
Paul
Gosh, Paul... I'm trying to help but you don't even test the code that you're providing before you post it. See anything wrong with the following snippet of code from the above?
[POL_COUNTRY_CD] [nvarchar](2) NULL,
[POL_LOCATION_CD] [nvarchar](5) NULL,
[POL_COUNTRY_CD] [nvarchar](2) NULL,
[POD_COUNTRY_CD] [nvarchar](2) NULL,
It also doesn't contain the extra location column you spoke of.
Please repair the sample code and make sure it and all the test data works. Then repost it and we'll give it another try.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 17, 2011 at 1:42 pm
Jeff,
I'm expecting the sample section to be
[POL_COUNTRY_CD] [nvarchar](2) NULL,
[POL_LOCATION_CD] [nvarchar](5) NULL,
[POD_COUNTRY_CD] [nvarchar](2) NULL,
[POD_LOCATION_CD] [nvarchar](5) NULL,
At least that's what the sample data look like.
The new columns are [POL_LOCATION_CD] and [POD_LOCATION_CD]. Those can be used in a join between NCV_BL_DHL_TEMP and MG_LOCATION.
The result would be a single row (assuming there are no dups for the location). This would return a single CONTINENT_CD.
However, if COUNTRY_CD in MG_LOCATION would hold FRS/FRN instead of FR, the link back to DHL_TRADE_ASSIGNMENT would be even easier. Hence the related question in my previous post. 😉
January 17, 2011 at 6:04 pm
LutzM (1/17/2011)
Jeff,I'm expecting the sample section to be
[POL_COUNTRY_CD] [nvarchar](2) NULL,[POL_LOCATION_CD] [nvarchar](5) NULL,
[POD_COUNTRY_CD] [nvarchar](2) NULL,
[POD_LOCATION_CD] [nvarchar](5) NULL,
At least that's what the sample data look like.
The new columns are [POL_LOCATION_CD] and [POD_LOCATION_CD]. Those can be used in a join between NCV_BL_DHL_TEMP and MG_LOCATION.
The result would be a single row (assuming there are no dups for the location). This would return a single CONTINENT_CD.
However, if COUNTRY_CD in MG_LOCATION would hold FRS/FRN instead of FR, the link back to DHL_TRADE_ASSIGNMENT would be even easier. Hence the related question in my previous post. 😉
And now you understand my question, as well. 🙂 Yes, I agree with the columns you posted. I just want the OP to agree, as well.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 18, 2011 at 3:08 am
HI Jeff,
Sorry about the mistake in the DDL, I have corrected the same in my earlier post...it should be:
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,
[POL_LOCATION_CD] [nvarchar](5) NULL,
[POD_COUNTRY_CD] [nvarchar](2) NULL,
[POD_LOCATION_CD] [nvarchar](5) NULL,
[SAISAN_VESSEL_CD] [nvarchar](6) NULL,
[SAISAN_VOYAGE_CD] [nvarchar](6) NULL,
[SAISAN_LEG_CD] [nvarchar](1) NULL,
[SAISAN_MONTH] [nvarchar](6) 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]
January 24, 2011 at 3:45 am
Hi,
Here is the example to use CTE(Common Table Expression)
You can use CTE to get unique country codes.
-- Lets say this sample table holds your final data with duplicate codes
Declare @t table(CODE varchar(10),REGION varchar(100))
insert @t SELECT 'FR','EURO'
insert @t SELECT 'FR','EURO'
insert @t SELECT 'GB','UK'
;WITH tmpcte AS
(
SELECT row_number() OVER(PARTITION BY CODE ORDER BY CODE ASC) as UniqueId,
CODE,
REGION
FROM @t
)
SELECT * FROM tmpcte
WHERE UniqueId = 1
--You would get below result when you run the above query.
1 -- FR -- EURO
1 -- GB -- UK
Hope this would shed some light to your issue.
Thanks
January 24, 2011 at 7:02 pm
pwalter83 (1/18/2011)
HI Jeff,Sorry about the mistake in the DDL, I have corrected the same in my earlier post...it should be:
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,
[POL_LOCATION_CD] [nvarchar](5) NULL,
[POD_COUNTRY_CD] [nvarchar](2) NULL,
[POD_LOCATION_CD] [nvarchar](5) NULL,
[SAISAN_VESSEL_CD] [nvarchar](6) NULL,
[SAISAN_VOYAGE_CD] [nvarchar](6) NULL,
[SAISAN_LEG_CD] [nvarchar](1) NULL,
[SAISAN_MONTH] [nvarchar](6) 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]
Sorry... I moved on to other threads and lost track of this one. Have you sussed this problem or is it still a problem.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 25, 2011 at 2:26 am
Hi Jeff,
Thanks for your reply..I am still stuck on this problem...dont know how to resolve this now....have tried everything and have really exhausted every option.
One more table has been added now as a lookup table which only has the values for FRN and FRS:
the DDL is as follows:
CREATE TABLE [dbo].[DHL_TRADE_ASSIGNMENT_FR](
[COUNTRY_CD] [nvarchar](20) NOT NULL,
[COUNTRY_DSC] [nvarchar](50) NULL,
[REGION_TRADE] [nvarchar](50) NULL,
[SUB_REGION] [nvarchar](50) NULL,
[POL_LOCATION_CD] [nvarchar](5) NULL,
[POD_LOCATION_CD] [nvarchar](5) NULL
) ON [PRIMARY]
the data for the above table is:
Insert into DHL_TRADE_ASSIGNMENT_FR
values('FRS', 'France South', 'EURO MED', 'EURO MED', 'FRMRS', 'FRMRS')
Insert into DHL_TRADE_ASSIGNMENT_FR
values('FRS', 'France South', 'EURO MED', 'EURO MED', 'FRFOS', 'FRFOS')
Insert into DHL_TRADE_ASSIGNMENT_FR
values('FRN', 'France North', 'EURO NC', 'EURO NC', 'FRLEH', 'FRLEH')
Important- Please use the following command for removing the FR rows in DHL_TRADE_ASSIGNMENT table
delete from DHL_TRADE_ASSIGNMENT
where country_cd in ('FRN', 'FRS')
Could you please help me on this one ?
Many thanks,
Paul
P.S.- Just an update, I have attached a query in which I am only able to retrieve data from one table, however, I need to retrieve data from both the tables, DHL_TRADE_ASSIGNMENT_FR(lookup table) and DHL_TRADE_ASSIGNMENT. Would it be possible for you to give it a look ?
Viewing 10 posts - 31 through 39 (of 39 total)
You must be logged in to reply to this topic. Login to reply