January 11, 2011 at 2:19 am
Hi Craig,
Thanks for your reply. The NCV_BL_DHL_TEMP table will be joined to MG_Location on
NCV_BL_DHL_TEMP.POL_COUNTRY_CD = MG_Location.COUNTRY_CD
Hope that helps.
Kind Regards,
Paul
January 11, 2011 at 2:28 am
Hello LutzM and Jeff,
Thanks a lot for your replies. One thing I forgot to mention is that this project is part of an SSIS package. If you are familar with SSIS, can this scenario be handled in a task before I run the main query.
I mean the FRS and FRN values need to be filtered before the main query anyways. Can something be done (in like say Execute SQL task in SSIS) so that I get separate values for FR before it goes to the main query ?
Thanks again !
January 11, 2011 at 2:31 am
pwalter83 (1/11/2011)
Hi Craig,Thanks for your reply. The NCV_BL_DHL_TEMP table will be joined to MG_Location on
NCV_BL_DHL_TEMP.POL_COUNTRY_CD = MG_Location.COUNTRY_CD
Hope that helps.
Kind Regards,
Paul
Precisely the issue. There's two FR rows and you're hooking by FR = FR... meaning you're going to duplicate. You don't have a keyed method into the MG_Location to pull back a single row to pull back a single continent.
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 11, 2011 at 3:24 am
Hi Craig,
The condition is if POL_COUNTRY_CD or POD_COUNTRY_CD in table NCV_BL_DHL_TEMP begins with FR, then would need to join to MG_LOCATION to identify continent.
If continent is equal to 'EUR' then would have to lookup 'FRN' in DHL_TRADE_ASSIGNMENT. If continent is equal to 'MED' then lookup 'FRS'.
But the following conditions also produce the values for FRN and FRS (because b.REGION_TRADE = 'EURO NC' also applies to FRN and b.REGION_TRADE = 'EURO MED' also applies to FRS), this then results in duplicate values for FR-
When b.REGION_TRADE = 'EURO NC' and c.Sub_Region = 'OTHERS EURO' THEN 'Intra Euro+MED'
When b.REGION_TRADE = 'EURO MED' and c.REGION_TRADE = 'ASPA' THEN 'Euro MED to ASPA'
When b.REGION_TRADE = 'EURO MED' and c.REGION_TRADE = 'SPAC' THEN 'Euro MED to SPAC'
When b.REGION_TRADE = 'EURO MED' and Left(A.POD_COUNTRY_CD,2)='US' THEN 'Euro MED to US'
When b.REGION_TRADE = 'EURO MED' and Left(A.POD_COUNTRY_CD,2)='CA' THEN 'Euro MED to CA'
When b.REGION_TRADE = 'EURO MED' and c.REGION_TRADE = 'AMLA' THEN 'Euro MED to AMLA'
January 11, 2011 at 4:12 am
I still think it's better to use th additional column (as suggested before), maybe in combination with the exception table I mentioned.
But the main issue still remains: how tro identify the correct value if you have duplicates based on the country code? That needs to be answered via business rules, not code.
To be more specific: What is the business rule to either use 'FRS' or 'FRN' if the country is FR?
This issue now is resolved by using DISTINCT, as far as I can see.
But what will you do if there is a scenario where you don't have identical results to use DISTINCT against?
What will yyou do with two different result sets for 'FR'?
January 11, 2011 at 4:18 am
pwalter83 (1/11/2011)
Hello LutzM and Jeff,Thanks a lot for your replies. One thing I forgot to mention is that this project is part of an SSIS package. If you are familar with SSIS, can this scenario be handled in a task before I run the main query.
I mean the FRS and FRN values need to be filtered before the main query anyways. Can something be done (in like say Execute SQL task in SSIS) so that I get separate values for FR before it goes to the main query ?
Thanks again !
As mentioned before, the main issues is to clearly describe the business case in general, not by using examples. Something like "If the LEFT(2) function will return more than a single row, do <insert rule here>."
Make sure the rule you describe is not a large CASE WHEN THEN list based on given data. Otherwise you'd need to change the rule just if a new value is inserted. Check if the rule applies to ALL duplicates, not just to a given example.
I recommend to step back, get the business logic fixed and decide the technical implementation method after that.
January 11, 2011 at 4:34 am
Hi Lutz,
I really have no answer to your questions and am stuck with no option but to resign from my current position here.
I actually made a bad choice when I entered this field of SQL programming and could have done better with SAP(never had a programming head therefore decided to go with functional SAP HR).
I have no in-depth knowledge of SQL and whatever I have gained has been with little experience that I got while working on projects. But on the whole I really have no interest in this field altogether. I wish to go back to SAP as it really involved no programming at all(that was the best part).
Sorry about all this moaning but I have no option left now whatsoever. Thanks a lot anyways for all your valuable time and effort and I apologize about my earlier comment.
Kind Regards,
Paul
January 11, 2011 at 5:14 am
Hi LutzM,
The issue is to include a new condition in the query that has already been created.
The condition is if value in POL_COUNTRY_CD or POD_COUNTRY_CD column in table NCV_BL_DHL_TEMP begins with FR, then join to MG_LOCATION table to identify continent. If continent is equal to 'EUR' then lookup 'FRN' in DHL_TRADE_ASSIGNMENT table. If continent is equal to 'MED' then lookup 'FRS'.
However, the problem is in my query based on the conditions, it would also display values for FR and hence result in duplicate values.
So I think it would be a better idea to filter out the condition first in SSIS. but how to achive that exactly is what I am not able to figure out.
January 11, 2011 at 5:14 am
pwalter83 (1/11/2011)
Hi Lutz,I really have no answer to your questions and am stuck with no option but to resign from my current position here.
I actually made a bad choice when I entered this field of SQL programming and could have done better with SAP(never had a programming head therefore decided to go with functional SAP HR).
I have no in-depth knowledge of SQL and whatever I have gained has been with little experience that I got while working on projects. But on the whole I really have no interest in this field altogether. I wish to go back to SAP as it really involved no programming at all(that was the best part).
Sorry about all this moaning but I have no option left now whatsoever. Thanks a lot anyways for all your valuable time and effort and I apologize about my earlier comment.
Kind Regards,
Paul
If your reply is based on my comments, then I'm sorry. It wasn't my intention to come across rude or anything like that. All I tried to say is the issues seems to be moer related to the business logic behind than the code itself.
As a side note: I don't understand your SAP comment at all. Even SAP (and specifically SAP) relies on business rules....
January 13, 2011 at 7:44 am
Hi Craig,
Could you please suggest something else as the code you mentioned does not yield the correct resultset ?
I am still stuck at the moment and have already tried different scenarios now.
Kind Regards,
Paul
January 13, 2011 at 9:04 pm
pwalter83 (1/11/2011)
The condition is if value in POL_COUNTRY_CD or POD_COUNTRY_CD column in table NCV_BL_DHL_TEMP begins with FR, then join to MG_LOCATION table to identify continent.
That's the whole problem in a nutshell. There has to be something in the NCV_BL_DHL_TEMP table that identifies the continent so we can join to the MG_Location table correctly. What is that something in the NCV_BL_DHL_TEMP table that identifies the continent?
Without that bit of information, I don't believe there's a way to do this.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 14, 2011 at 9:06 am
Hi Jeff,
Here is the updated scenario:
If POL_COUNTRY_CD or POD_COUNTRY_CD in NCV_BL_DHL_TEMP table begins with FR, then use POL_Location_CD / POD_Location_CD and join to MG_LOCATION table to identify continent. If continent is equal to 'EUR' then lookup 'FRN' in DHL_TRADE_ASSIGNMENT. If continent is equal to 'MED' then lookup 'FRS'.
Would this be of any help ?
Regards,
Paul
January 14, 2011 at 10:57 am
pwalter83 (1/14/2011)
Hi Jeff,Here is the updated scenario:
If POL_COUNTRY_CD or POD_COUNTRY_CD in NCV_BL_DHL_TEMP table begins with FR, then use POL_Location_CD / POD_Location_CD and join to MG_LOCATION table to identify continent. If continent is equal to 'EUR' then lookup 'FRN' in DHL_TRADE_ASSIGNMENT. If continent is equal to 'MED' then lookup 'FRS'.
Would this be of any help ?
Regards,
Paul
Until you don't have two rows with 'FR' in MG_Location this logic will not save you. You cannot identify which of these two rows you want from MG_Location. If you don't correct this, you cannot fix this using the technique you describe.
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 14, 2011 at 6:13 pm
pwalter83 (1/14/2011)
Hi Jeff,Here is the updated scenario:
If POL_COUNTRY_CD or POD_COUNTRY_CD in NCV_BL_DHL_TEMP table begins with FR, then use POL_Location_CD / POD_Location_CD and join to MG_LOCATION table to identify continent. If continent is equal to 'EUR' then lookup 'FRN' in DHL_TRADE_ASSIGNMENT. If continent is equal to 'MED' then lookup 'FRS'.
Would this be of any help ?
Regards,
Paul
Neither POL_Location_CD or POD_Location_CD is in any of the tables in your first post. Would you repost the table that these two columns are in along with some test data which contains the rows we'd need to find for FR?
I also agree with Craig... you have two rows in the MG_LOCATION table with a country of FR and a continent of MED. Unless there's another qualifier for the join, there's still no way to resolve this problem.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 15, 2011 at 2:46 am
The sample data can be found here (it's the Edit: 11th post of this thread).
Regarding the MG_LOCATION: not only there is more than one value for MED. It's also not clear what condition will be used to decide if either MED or EUR needs to be used.
Viewing 15 posts - 16 through 30 (of 39 total)
You must be logged in to reply to this topic. Login to reply