Monday, April 20, 2009

SQL Wildcards

We implemented Multicurrency module and part of this implementation is changing the naming convention of our customers in Great Plains. We want to easily identify what kind of customer are they. Currently, our customer id ends in 001 so we decided to change it to whatever the contract currency is. So let's say ABC has a EURO contract with us then their customer id will then be ABCEUR instead of ABC001.

Our database is shared to other systems like our BI team to create relational DBs. A question came to my lap on the easiest way to segregate the customers based on their contract currency. We don't want to give them the Currency ID field as it's not consistent with other system. So I provided them with this Select statement:

Select * from RM00101
where custnmbr like '%[EUR]'

3 comments:

Nick Matyas said...

Good post !!!! very good blog . nice article.i like that.


webroyalty

In-Consulting said...

What about clients using more than one currency?
Would have been better to create a view or sp which gave the BI team the list of clients the way you wanted it portrayed, and then given them access to just that view or sp.

Any good implementation would have currencies set up as EUR, GBP, etc. so the associations should be simple.

lily said...

Possibly the most amazing blog that I read all year dresses with sleeves!?!