I think I figured out a trick using [Ice].entry() that might work, though it might get kind of convoluted still.
You can set up a small CTE to generate a table of row numbers to start with, let’s call it ListGen:
- Pick a table guaranteed to have as many rows as you need (how many options can users pack into that comma-separated parameter?), and pull it in. Under SubQuery Options, go with Result Set Rows: Top and set the Rows Number to that amount.
- Under Display Fields, add the Company column from the table you picked and then a calculated integer field (let’s call it RowNum):
row_number() over(order by <table>.<some ID column>
e.g.,
row_number() over(order by PartTran.TranNum)
This CTE should be able to be shared by however many things you need to check, as it’s basically just providing a table of the current Company ID and some row nums. Make another CTE to split the parameter with your list and generate a table with the individual items in the list (let’s call this CTE SplitList):
- Pull in the ListGen subquery.
- For Display Fields, bring in that Company field and make a new calculated field, nvarchar of whatever size is the longest any one item in the list parameter can be:
[Ice].entry(ListGen.Calculated_RowNum, @q, ',')
-- @q is the BAQ parameter with a comma-separated list of strings
-- any wildcards to be used with LIKE need to be included
-- something like '%123456%,%100000%,%etc%'
-- or they can be added in this calculated field
- Back to the Phrase Build tab, down below SubQuery Criteria, add a criterion like:
Calculated StrPart <> '' expressionto filter out blank rows when@qcontains fewer items than theTOP n ROWSnumber.
Now you can bring the SplitList CTE into any other subqueries and join against it using a LIKE operation or whatever:
(Shout out to String_split in BAQ - #4 by ckrusen for the idea)