OLEDB positional parameters with a WHERE IN clause
We have FX rates published in a table to/from virtually every currency in the world. However, this is something like 70k rates a month and the vast majority are for currencies we don't use in our OneStream application. The majority of the rest are for combinations that we don't use. Of these tens of thousands of rates, only about 25-30 are actually relevant.
I'm trying to automate this filtering using the currency filter from the application properties, which returns a simple comma-separated list, like so: CAD,EUR,JPY,USD
In this example, I want all rates to/from USD. So, I ditch the ",USD" and want to filter on the three remaining currencies: CAD,EUR,JPY
Writing a query in SSMS, this is trivial. Writing the query with straight up string manipulation in the BR is also straightforward. However, I'm trying to be (perhaps needlessly strict/cautious) by passing this in as a parameter. This makes me want to use my laptop as a non-brand-specific flying disc and restart life as a hermit in a cave with no electricity. Un-typed positional parameters in an OLEDB connection are a nightmare (but probably because I need to learn more.)
My basic thought is something like this:
BRApi.Database.ExecuteSqlUsingReader(dbConnInfo, sql, params, False)SELECT FromCurrency,ToCurrency,Rate FROM FxRateTable
WHERE (FromCurrency IN (?) AND ToCurrency = 'USD') OR (ToCurrency IN (?) AND FromCurrency = 'USD')I parsed the currency list to include single-quotes around the currencies and then just passed in P1 and P2 parameters as the same value so it should evaluate to a very normal WHERE IN clause. I get zero results from within OS. If I hardcode the list of currencies (comma-separated with single-quotes) in place of those ?s in the BR, the code succeeds and updates the rates in my OS application.
I can only assume there's some type of data type problem that isn't making the translation through the OLEDB driver.
I can conceive of a several possible avenues of investigation:
- There's a way of passing in these currencies as a string and I'm just doing something wrong that should be obvious.
- I need to add data-typing code in the BR (and perhaps store the currencies in a compatible data type) for OLEDB to play nice.
- I switch all of our external DB connections to SqlServer native so I can pass in named, typed parameters. (In this case, the docs only have instructions for setting up OLEDB, not native SqlServer. Does anyone have info on this?)
Ugh, I finally banged my face on the keyboard enough that I got a relevant search result.
The missing tool was the STRING_SPLIT function. It can be used in the above example, like so:
SELECT FromCurrency,ToCurrency,Rate FROM FxRateTable WHERE (FromCurrency IN (SELECT value FROM STRING_SPLIT(?,',')) AND ToCurrency = 'USD') OR (ToCurrency IN (SELECT value FROM STRING_SPLIT(?,',')) AND FromCurrency = 'USD')Alternatively, you can declare a @variable = ? and then string_split on the @variable instead of passing in both P1 and P2 with the same value.
