The IN Operator is a very commonly used function in SQL to subselect items based on a list of values. it is extremely useful, for example, if you were trying to synchronize a data set from an external system with data inside Acumatica/SQL Server. The Acumatica development blog acknowledged this usefulness in its example code when it announced the availability of the IN Operator: SQL In Operator in BQL - Acumatica Developers Blog
What it glosses over is that there is no way to use this operator with a Select2 query, which means there is no way to use it with a PXProjection for doing advanced data views in Acumatica. This requirement is not documented anywhere, except for the faint mention that it must be used with the Required<> property. The consequences of only being available with the Required property is that it must be used with a PXSelect type query, but cannot be used with a Select2 type of query. Select2 query style is required for any kind of PXProjection in the system. PXProjection’s are used extensively in Acumatica for advanced data views (the Add Items functionality of the main Sales Order screen uses a PXProjection).
Furthermore, if IN is used in a Select2 query, the query will compile and SQL Server will parameterize it (verified using SQL Server Profiler). So SQL Server will build the the myField IN () part of the query, but has no way to populate the values. Therefore, this operator can be “used” with Select2, but it will never actually work correctly. If it cannot be used with Select2, then trying to use it should throw an error.
Even worse, it appears that a separate function, In3, was created to hack around this problem. In3 uses constants set prior to runtime, and CAN be used with Select2 type queries (NotIn3 is actually used in the Inventory Items Select2 PXProjection query). However, constants must be hardcoded, and cannot be set at runtime, and there is a maximum number of values that can be included in an In3 query (also undocumented, but I think the max is 3). Of course, there is no requirement to use constants in native SQL Server, this is just something Acumatica enforces for some unknown reason.
Anyway, this is an ungodly mess, for what should be a very straightforward query feature. it is very straightforward in SQL Server itself. Please clean this up and make it work correctly. It is very annoying to have a SQL abstraction that masquerades as full-featured but is full of holes.
Best answer by Dmitrii Naumov
View original