Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.3K Intelligent Advisor
- 62 Insurance
- 536.1K On-Premises Infrastructure
- 138.2K Analytics Software
- 38.6K Application Development Software
- 5.7K Cloud Platform
- 109.4K Database Software
- 17.5K Enterprise Manager
- 8.8K Hardware
- 71.1K Infrastructure Software
- 105.2K Integration
- 41.6K Security Software
Mapping query

Hi,
I have a list of divisions [01 to 17]
How do I write a mapping condition that applies to all but "08"?
I would prefer not to define the upper limit in case "18" appears down the track.
I need something like
- NOTIN "08" or
- 01>07 AND 09+
TIA
Best Answer
-
Besides some of the methods that have already been provided there is also the option of a single SQL mapping to handle it.
Example based on entity but it would be the same concept for any dimension.
Cheers
John
Answers
-
Hi,
Here are two ways to do this:
1) Create two BETWEEN Mappings: one for 01 to 07 and one for 09 to the upper limit that works. If there aren't any other mappings for other division, you could set it to 99
2) If you want to map 08 to a specific value, you could create one EXPLICIT mapping which would take care of your exceptions for the 08 division and then you could create a LIKE * mapping to take care of anything else.
Hope this helps, otherwise please provide some more detail.
Regards,
Matthias
-
Besides some of the methods that have already been provided there is also the option of a single SQL mapping to handle it.
Example based on entity but it would be the same concept for any dimension.
Cheers
John
-
Hi John,
Is sql query mean something like case when etc... But how do we denote source mapping and assign to target mapping etc?? Are there any keywords to refer source and target??
Thanks
Amith
-
The source and target are referred to in the example CASE statement, it is basically saying if the entity source value is '08' then the target value will be 'Map 08'
If you want to know know more have a look at Conditional Mapping using a Mapping Script
-
-
Ah.. Thanks John got it now.
-
Hi @JohnGoodwin
Thank you for the suggestion. I got one example of this to work, but I am having trouble with my next requirement (syntax below). When I don't include this rule, every account maps as intended. When I include this rule, a heap of accounts are left un-mapped. I can't find any logic as to why some are still mapped using existing rules and others are not. Any ideas?
-
-- add this between your last when and the end
else 'Change This for all other codes'
or can be
else ACCOUNT (if your default is to do nothing but use the straight account)
or add as many 'When' clauses as it takes to cater for all your accounts....
-
The column will not be "DEPARTMENT", you need to check the "Data Table Column Name" in target application dimension details, it is possibly "ENTITY" or one of the UD columns
-
Thank you @JohnGoodwin, it should be 'UD2', however there is something wrong with the first WHEN statement. It doesn't seem to recognise ....IN '01,02,06'.....as a list of values
The 2nd WHEN statement with ...IN '8'... works correctly