December 21, 2010 at 9:09 am
Actually it is part of the formatting of this message as they go all jumbled up without the dashes....
you may be right but the thing is I also have to bring the columns, POL_COUNTRY_CD and POD_COUNTRY_CD into consideration.
if you noticed I started my query as -
TRADELANE=
case ltrim(rtrim(substring(a.POL_COUNTRY_CD,1,2) ))
The solution you mentioned would not fit into it now as I have to use the above scenario and use it as the first part of the case statement for 'Any country where Region/Trade = EURO MED'.
Got any other ideas ??????
December 21, 2010 at 9:20 am
Actually yes, forget SQL for 2 seconds and write the pseudo code with simple ifs.
I think your problem is that you can't express the business rule, not that you don't know how.
Also keep in mind that you may have to do that update in 2 passes. Solve 1 problem, then solve the rest in another go.
(You can always remerge the logic once then, but it helps my thinking process when I do that).
December 21, 2010 at 2:43 pm
It seems like there are three options to consider: a lookup table, a (more or less) simple string concatenation or a combination of both.
If you write T-SQL code you should always keep in mind that the code should not (must not) be modified if new values are entered in the underlying table(s). You might need to update a lookup table based on an inserted row in another table. But the code for a view, function or even stored procedure should (must) remain untouched. If there is a need to modify some code based on new values in a table it's either an indicator of a poor database design, poor code or even both. (There are exceptions, of course... 😉 ).
Unfortunately, the information provided so far doesn't really help to provide a coded solution.
I'd recommend you take a look at the first article referenced in my signature and psot some rady to use sample data together with your expected result based on those data.
Or follow Ninjas advice and post the pseudo code (even then, ready to use sample data would really help...)
December 22, 2010 at 2:45 am
Hi LutzM,
I don't know what I am missing here. Can I just show you an example of what I am trying to do:
This is the table based on which I have created a code-
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 is the code-
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
Is it possible to tweak the above code to accomodate the following requirement now:
TRADELANE-----------POL_COUNTRY_CD------ --------------------POD_COUNTRY_CD
Intra Euro+MED---Any country where Region/Trade= EURO MED---Any country where region/Trade = EMA
The reason I ask is now instead of the 'Starts with GB' it now says 'Any country where Region/Trade= EURO MED'.
This is all I want to ask and I am stuck with it for the moment. Can you please help me with this ?
December 22, 2010 at 2:51 am
Please study this [/url]link. It should help you understand why we cannot understand what you want and how to rectify that.
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 22, 2010 at 3:02 am
Hi Paul
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
--------------------------------------------------------------------------------------------------
TRADELANE-----------POL_COUNTRY_CD------ --------------------POD_COUNTRY_CD
Intra Euro+MED---Any country where Region/Trade= EURO MED---Any country where region/Trade = EMA
--------------------------------------------------------------------------------------------------------
Try the following SQL Query
select distinct a.BL_ID,a.POL_COUNTRY_CD,a.POD_COUNTRY_CD,b.REGION_TRADE,c.REGION_TRADE
TRADELANE=
case ltrim(rtrim(substring(a.POL_COUNTRY_CD,1,2) ))
when 'GB' then
case ltrim(rtrim(substring(a.POD_COUNTRY_CD,1,2) ))
when 'US'then 'Euro NC/UK to US'
when 'CA'then 'Euro NC/UK to CA'
else
case b.REGION_TRADE
when 'SPAC' then 'Euro NC/UK to SPAC'
when 'ASPA' then 'Euro NC/UK to ASPA'
when 'AMLA' then 'Euro NC/UK to AMLA'
when 'EMA' then 'Euro NC/UK to EMA'
end
end
when 'US' then
case ltrim(rtrim(substring(a.POD_COUNTRY_CD,1,2) ))
when 'GB' then 'US to Euro NC/UK'
else
case b.REGION_TRADE
when 'ASPA' then 'US to ASPA'
when 'EURO MED' then 'US to Euro MED'
when 'AMLA' then 'US to AMLA'
when 'EMA' then 'US to EMA'
when 'BLACK SEA' then 'US to BLACK SEA'
end
end
else case c.REGION_TRADE when 'EURO MED' then
case b.REGION_TRADE when 'EMA' then 'Intra Euro+MED' 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)))
inner join DHL_TRADE_ASSIGNMENT c
on a.POL_COUNTRY_CD = ltrim(rtrim(substring(c.COUNTRY_CD,1,2)))
Hope this will solve your problem
Incase any query let me know
Regards
Rashmi
December 22, 2010 at 4:22 am
Hi Rashmi,
I cant really thank you enough. You have really made my day today....I had asked this many times now and each time I got back the reply that my query wasnt clear enough.
Now two things are clear to me ...1st my query was sufficiently enough to be understood and 2nd ly and most importantly- you are really some kind of a Genius !!!! Not only did you resove this without even looking at the database but you were the only ONE in the forum to get back with a positive and right answer twice.
Thank you and May God Bless you !!!!!
Warm Regards,
Paul
December 22, 2010 at 4:24 am
pwalter83,
please stop reposting the same stuff over and over again.
You've been asked multiple times to post some additional information and you've been pointed at a link multiple times that will help us help you.
I'll step back until there's new information. I still think a lookup table and/or string concatenation will do it.
@ rashmiptl22:
Do you really recommend a solution where you have to modify the code each time a new POL_COUNTRY_CD value is inserted??
December 22, 2010 at 6:04 am
Hi Paul
Thank You so much for such a nice complement and warm regards.
Rashmi
December 22, 2010 at 6:57 am
LutzM (12/22/2010)
pwalter83,please stop reposting the same stuff over and over again.
You've been asked multiple times to post some additional information and you've been pointed at a link multiple times that will help us help you.
I'll step back until there's new information. I still think a lookup table and/or string concatenation will do it.
@ rashmiptl22:
Do you really recommend a solution where you have to modify the code each time a new POL_COUNTRY_CD value is inserted??
Same feeling here.
Not only did you NOT learn how to debug a simple problem by yourself, but you're learning the wrong way to do it which will never work long term.
Which is what I and Lutz really wanted you to learn.
So shall we continue to help or you're happy not doing your job right?
December 22, 2010 at 8:40 am
I tried my best to explain the problem in short and in detail as well. Rashmi was able to understand it the first time when I posted it and she even gave me a solution. Do you really think it has something to do with my not being able to explain the problem ? I dont think so....
I dont think there is anything else I can do in order to try to explain it again and again...really very very exhasted to do that now.
One thing I would say is if you do not want to provide a solution, it would be better if you could just keep it to yourself and not be philosophical. This way you block the post from others who really want to answer the query.
December 22, 2010 at 9:00 am
Thanks a lot for your answer Rashmi, but now I am stuck with 15 nested case statements (and increasing) which exceed the total 10 allowed in SQL....as there is a lot of data in the table that needs to be added.
Is it possible construct this by using temporary variables and then storing the value...this way even if the values keep on increasing in the table in the near future, I would not have to manually change the stored procedure everytime. I have also attached the spreadsheet which shows all the values that need to be added.
Many thanks,
Paul
December 22, 2010 at 9:33 am
pwalter83 (12/22/2010)
Thanks a lot for your answer Rashmi, but now I am stuck with 15 nested case statements (and increasing) which exceed the total 10 allowed in SQL....as there is a lot of data in the table that needs to be added.
Which is what Phil, Lutz and Ninja were trying to help you avoid. Believe me, there is a better solution out there, and they can help you with it. However, you need to HELP US HELP YOU.
FWIW, I do believe that Lutz is on the right track to make this work. Please check out the link that he has requested you to read, and post the information that he's requesting. Remember, he is trying to help you! (For your convenience, that link is also the first link in my signature block below.)
(For you to ponder: why do you think so many of us have the same link in our signature blocks?)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
December 22, 2010 at 9:39 am
Hi LutzM,
What exactly do you need from my side to resolve this issue ?
What I am trying to do is to populate a newly created column called Tradelane based on the values contained in a spreadsheet (Tradelane) - Using table DHL_TRADE_ASSIGNMENT and POL_Country_CD / POD_Country_CD from table NCV_BL_DHL_TEMP and assign DHL Tradelane to each BL_ID.
This is to be achieved using columns contained in 2 tables- DHL_TRADE_ASSIGNMENT (Region_Trade and Sub_Region)and NCV_BL_DHL_TEMP (BL_ID, POL_COUNTRY_CD and POD_COUNTRY_CD ).
Below is the sample query I created which could be helpful:
select distinct a.BL_ID,a.POL_COUNTRY_CD,a.POD_COUNTRY_CD,b.region_trade as POL_REGIONTRADE, c.region_trade as POD_REGIONTRADE,
b.Sub_Region as POL_SUBREGION, c.Sub_Region as POD_SUBREGION,
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' end
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)))
Please find attached the Tradeland.xls(spreadsheet), data structures for DHL_TRADE_ASSIGNMENT and NCV_BL_DHL_TEMP tables and sample data for both of them.
If you can help that would be great..I am stuck here for a long time now as this project is a combination of SSIS and SSRS.
Thanks and Regards,
Paul
December 22, 2010 at 9:59 am
pwalter83 (12/22/2010)
Hi LutzM,What exactly do you need from my side to resolve this issue ?
...
Please read and follow the instructions given in the first article referenced in my signature, or for your convenience, just click Here[/url]
Edit: Please note that I'm not going to download any xls file from an unknown source.
I know you're frustrated by now. But for sure, you're not the only one...
Viewing 15 posts - 16 through 30 (of 40 total)
You must be logged in to reply to this topic. Login to reply