December 17, 2010 at 2:15 am
Hello Friends,
Could you please help me create a SQL query for one of the steps in the functional specs I have for my project:
The scenario is - using fields from 2 tables I have to create a query which goes as :
''Assign Tradelane to each BL_ID ('NCV_BL_DHL_TEMP' table) using
fields - POL_COUNTRY_CD ('NCV_BL_DHL_TEMP' table) and POD_COUNTRY_CD ('NCV_BL_DHL_TEMP' table) and
COUNTRY_CD ('DHL_TRADE_ASSIGNMENT' table). Also, any BL_ID where it is not possible to map/set a tradelane - set tradelane as 'OTHERS'.''
Please find the table definitions below:
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
)
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 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
I hope this infiormation would help.
Thanks and Regards,
Paul
December 17, 2010 at 5:57 am
''Assign Tradelane to each BL_ID ('NCV_BL_DHL_TEMP' table) using
fields - POL_COUNTRY_CD ('NCV_BL_DHL_TEMP' table) and POD_COUNTRY_CD ('NCV_BL_DHL_TEMP' table) and
COUNTRY_CD ('DHL_TRADE_ASSIGNMENT' table). Also, any BL_ID where it is not possible to map/set a tradelane - set tradelane as 'OTHERS'.''
Not able to understand the requirement, need's more clarity.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply