SQL In <> Operator in BQL

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,
In<Required<InventoryItem.itemStatus>>>>.Select(Base, values);

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!

2 Replies to “SQL In <> Operator in BQL”

  1. 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:

Leave a Reply

Your email address will not be published. Required fields are marked *