Hi There,
Today want to show you example of amazing and very new BQL Operator – In<>.
This operator was added just recently with Acumatica version 2017R2, so now you can pass there an array of values and Acumatica core will convert it to SQL IN ( … ) statement.
Here is a code example:
Object[] values = new String[] { "AC", "IN" };
InventoryItem item = PXSelect<InventoryItem,
Where<InventoryItem.itemStatus,
In<Required<InventoryItem.itemStatus>>>>.Select(Base, values);
Console.WriteLine(item.Descr);
This will be converted to following SQL
Select * from InventoryItem InventoryItem
Where InventoryItem.Status In ('AC', 'IN')
Order by InventoryItem.InventoryCD
Please note that In<> operator is available only with Required<> parameter and you need to pass array of possible values manually to Select(…) method parameters.
Have a nice development!
Can we please fix this feature so that values can be passed at runtime using the Select2 query type that is available for PXProjections (important for advanced sql views)? This operator being tied to Required means it can only be used with a PXSelect. However, using this operator with Select2 will still compile and SQL Server will parameterize the query, but it is impossible to pass values in to the IN clause, so it effectively creates a broken query.
More information is in this StackOverflow post:
https://stackoverflow.com/questions/69829845/how-to-use-bql-in-operator-with-select2-query-pxprojection-and-list-of-values?noredirect=1#comment123439656_69829845
How would you use the Required in Fluent rather than standard BQL?