Forum Discussion
Solved
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 One...
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.
If you want to code a better technical solution maybe peruse the following article which is a good reference for parametrizing queries properly ...
SELECT Like a Boss With Query Parameters - OneStream Community (onestreamsoftware.com)
Also if you are querying from a single table you typically don't have to craft your own SQL statement just use the following Api method BRApi.Database.GetCustomDataTable(si, dbLocation, TableName, dbWheres, dbOrderBys)
In addition as you have only provided quite a limited code snippet I don't know how you are handling scope control for the returned data table so this is just a friendly reminder to make use of the Using statement which will also manage disposal of the data table resource when you have finished with it.