Forum Discussion

photon's avatar
photon
Contributor
2 months ago
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...
Admin
Connectors
Extenders
  • photon's avatar
    photon
    2 months ago

    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.

photon's avatar
photon
Contributor
2 months ago

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.

photon's avatar
photon
Contributor
31 days ago

It may be worth pointing out that this is basically a data type fix as the subselect using the string_split function is just turning a string into a data type that the IN clause can use. I still kinda' don't love it as it would feel a lot cleaner (and self-documenting) if I could pass in the right data type to start with and use it natively but it's an effective workaround until someone else comes along and proposes a more "technically correct" solution.