I would like to do a “range lookup”. Because I can't solve it using a conditional lookup, I used a custom field transformation. My code looks like this:
WHEN substring([SBIcode_cleansed], 1, 1) like '[^0-9]' THEN
where [SBIcode] = [SBIcode_cleansed]
where substring([SBIcode_cleansed], 1, 2) >= [min_sbi_code]
and substring([SBIcode_cleansed], 1, 2) <= [max_sbi_code]
It seems that a subquery is not possible because I get an error message that more than one value is retrieved using the subquery but that is not possible.
Testing it in SSMS works fine.
(Not using between because above seems to work faster when testing it in SSMS)
Perhaps I need to use a view in these cases but I would like to know what options I have.
I contacted support but they mentioned that I should asked the question in the community.
So hopefully someone can help.
Best answer by tldView original