December 21, 2010 at 5:15 am
Hello Friends,
I am trying to use wild character (%) with the CASE statement as below but it doesnt seem to work. Could someone please help me with this ?
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
Many thanks and regards,
Paul
Paul
December 21, 2010 at 5:18 am
Saying that something "doesn't work" is not very helpful. What pattern are you trying to match?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
December 21, 2010 at 5:25 am
Hi Phil,
I am sorry about the incomplete info. Actually I am trying to match the 'All' pattern as in :
'Any country where Trade = EURO MED'- this would mean all the countries whose Trade is assigned as 'EURO MED'
Thanks and Regards,
Paul
December 21, 2010 at 7:01 am
But I don't understand why you are even referring to country if you are not filtering on it?
select * from country where trade = 'euro med' and country = [anything]
is the same as
select * from country where trade = 'euro med'
Can you write out the logic you require more clearly?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
December 21, 2010 at 7:06 am
Let me try another variation of the same problem.
Are you trying to match all people that speak ALL those languages : French, Spanish, English?
December 21, 2010 at 7:56 am
Hi,
What I am simply trying to do is use a wild character with the CASE statement to achieve this scenario-
I have a table with a list of countries. Here is the sample of that-
CountryCode---Country Desc-------Region/trade
AD-------------Andorra------------EURO MED
AL-------------Albania-------------EURO MED
ES-------------Spain--------------EURO MED
TN------------Tunisia--------------EMA
TR------------Turkey--------------EMA
What I want to do is display all the countries which have a Region/Trade as 'EURO MED' and this is to be done within a CASE statement. So I have devised the following code which doesnt seem to work. The '%%' denotes the country code.
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
I hope this information helps now.
Regards,
Paul
December 21, 2010 at 8:00 am
I seriously don't understand why you cannot simply do
SELECT * FROM Tbl
where [Region/trade] = 'EURO MED'
Then join to other tables if you're missing data.
December 21, 2010 at 8:10 am
Hi,
If its possible, could you please tell me whether it is possible to use '%' as wild character with the CASE statement as in the code I have mentioned ? And how can it be achieved ?
The code you provided as solution is a basic one and it is not wrong but what I am doing here is a part of a bigger project and wont work with what I want to achieve.
Thanks and Regards,
Paul
December 21, 2010 at 8:16 am
Yep, I've read through a few times and I'm baffled by this one ...
As well as providing sample input data, can you provide your desired output please? No need to attempt a CASE expression, as you are confusing, rather than illuminating, the problem with it.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
December 21, 2010 at 8:17 am
SELECT
Whatever
FROM
dbo.tbl
WHERE
1 = CASE WHEN Col LIKE '%your string here%' THEN 1
ELSE CASE WHEN 'Whatever 2' = 'continue logic here' THEN 1
ELSE 0
END
END
December 21, 2010 at 8:17 am
pwalter83 (12/21/2010)
Hi,If its possible, could you please tell me whether it is possible to use '%' as wild character with the CASE statement as in the code I have mentioned ? And how can it be achieved ?
Thanks and Regards,
Paul
No. Your logic does not scan.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
December 21, 2010 at 8:23 am
Okay guys, No worries..thanks for your suggestions...I guess I have to go about it on my own..
December 21, 2010 at 8:25 am
pwalter83 (12/21/2010)
Okay guys, No worries..thanks for your suggestions...I guess I have to go about it on my own..
No need to.
What you need to do is make us understand what you need to do. Hence we need to see the actual data and the required output.
I find that when I cannot explain something clearly to someone else, it usually means that I don't understand myself. And that is possibly your biggest problem with this issue.
December 21, 2010 at 8:41 am
Thanks for your consideration....here is the detailed version of what I am trying to do-
-------------------------------------------------------------------------------------------------
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
--------------------------------------------------------------------------------------------------
The solution to the above is as follows-
select distinct 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 'SPAC' then 'Euro NC/UK to SPAC'
else case b.REGION_TRADE when 'ASPA' then 'Euro NC/UK to ASPA'
else case ltrim(rtrim(substring(a.POD_COUNTRY_CD,1,2) )) when 'US' then 'Euro NC/UK to US'
else case ltrim(rtrim(substring(a.POD_COUNTRY_CD,1,2) )) when 'CA' then 'Euro NC/UK to CA'
else case b.REGION_TRADE when 'AMLA' then 'Euro NC/UK to AMLA'
else case b.REGION_TRADE when 'EMA' then 'Euro NC/UK to EMA'
else 'Others' end end end end end end
when 'US' then case b.REGION_TRADE when 'ASPA' then 'US to ASPA'
else case ltrim(rtrim(substring(a.POD_COUNTRY_CD,1,2) )) when 'GB' then 'US to Euro NC/UK'
else case b.REGION_TRADE when 'EURO MED' then 'US to Euro MED'
else case b.REGION_TRADE when 'AMLA' then 'US to AMLA'
else case b.REGION_TRADE when 'EMA' then 'US to EMA'
else case b.REGION_TRADE when 'BLACK SEA' then 'US to BLACK SEA'
else 'Others' end end end end end end
when 'CA' then case ltrim(rtrim(substring(a.POD_COUNTRY_CD,1,2) )) when 'GB' then 'CA to Euro NC/UK'
else case b.REGION_TRADE when 'EURO MED' then 'CA to Euro MED'
else case b.REGION_TRADE when 'ASPA' then 'CA to ASPA'
else case b.REGION_TRADE when 'AMLA' then 'CA to AMLA'
else case b.REGION_TRADE when 'EMA' then 'CA to EMA'
else 'Others' end end end end end
end
from NCV_BL_DHL_TEMP a
INNER JOIN
DHL_TRADE_ASSIGNMENT b
on a.POD_COUNTRY_CD = ltrim(rtrim(substring(b.COUNTRY_CD,1,2)))
-------------------------------------------------------------------------------------------------
Now what I want to do is write a query for this part-
TRADELANE-----------POL_COUNTRY_CD------ --------------------POD_COUNTRY_CD
Intra Euro+MED---Any country where Region/Trade= EURO MED---Any country where region/Trade = EMA
I would completely understand if you seem disinterested now due to the length of this message. Any help would be deeply appreciated.
December 21, 2010 at 8:53 am
want to see a really long query????
Just one last question. Are all those dashes (-) part of formatting for this message or is it part of the data?
Maybe something you don't know is possible. You can write it like this :
CASE when Region/Trade= 'EURO MED' OR region/Trade = 'EMA' THEN ....
Viewing 15 posts - 1 through 15 (of 40 total)
You must be logged in to reply to this topic. Login to reply