Hello everyone -
I have a table with the fields: DBA (Doing Business As), Merchant Wildcards (which will correspond to a Merchant Name), and Merchant Codes. For example:
| DBA | Merchant Wildcard | Merchant Code |
|---|
| Bob's General Store | Merchant 1* | 1234 |
| Bob's General Store | Merchant 1* | 4567 |
| Bob's General Store | Merchant 4* | 1234 |
| Frank's Grocery Store | Merchant 2* | 1234 |
| Frank's Grocery Store | Merchant 2* | 4567 |
I have a data set that contains the Merchant Name, Merchant Code, and many other fields at the individual transaction level. I need to load any transaction that is like one of the merchant wildcards above AND matches the corresponding merchant code.
For example, this transaction would be loaded, and would map to Bob's General Store:
| Merchant Name | Merchant Code |
|---|
| Merchant 1abc | 1234 |
These transactions would not be loaded at all:
| Merchant Name | Merchant Code |
|---|
| Merchant 3abc | 1234 |
| Merchant 1abc | 8976 |
My initial thought how to do this is to use two loops:
- loop by each DBA
- loop by each Merchant Wildcard
- match on any Merchant Code for that Merchant Wildcard
Until I have looped through each combination of merchant wildcard's and merchant codes, for every DBA.
My questions:
1) Can you store a wildcard into a variable, which the like operator can evaluate against?
2) Is there a like function that lets you evaluate one record against multiple wildcard values?
3) Any ideas on how to do this more efficiently? Open to any idea here.
Your help is appreciated.
Thanks.
