Solved

Please uncripple the Acumatica BQL IN Operator so it can be used with Select2 type queries

  • 4 November 2021
  • 4 replies
  • 196 views

Userlevel 6
Badge +2

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.

icon

Best answer by Dmitrii Naumov 23 December 2021, 08:14

View original

4 replies

Userlevel 6
Badge +1

Well, In3 operator does support at least up to 10 values. 

It also does not need constants specifically, you can put any type of BQL operand as a parameter.

However, I’m not sure how you’d like to set parameters at runtime if there is no way to send any parameters to Select2 type of queries, except for CurrentValue (there is also no way to pass parameters in SQL views declaration, right?).

 

There is also In2 operator that allows you to do subqueries:

https://help-2021r2.acumatica.com/(W(104))/Help?ScreenId=ShowWiki&pageid=72a1d03c-75f1-7edd-907f-5b8a529d61de

 

BQL may be frustrating at times. It is indeed a little bit more complicated than straight SQL. But it is required to sacrifice something in order to implement ORM+query language in C# classes with all the requirements. So, I’m not sure what you are suggesting here. If there is a specific suggestion, I’d recommend posting it in Ideas section (https://community.acumatica.com/ideas).

 

P.S. There is also FBQL that is supposed to be a bit easier for the developers who are not used to BQL.

 

 

 

Userlevel 6
Badge +2

Hi Dmitry-

This is kind of a corner case situation. It involves the use of PXProjection, which puts us back in the realm of ad-hoc queries to the database (there is not tight coupling of the ORM to a specific table in this situation). In my experience, I was not able to get In3 to work with values set at runtime. You can use them in In3, but they will not work. They must be constants set before runtime (see Framework Development guide definition for In3:

<Table.field, In3<TConst1, ..., TConstN»

If you know of a way to inject values at runtime into In3 that works, I would be interested in seeing that code.

If you want to boil down the problem here, the issue really comes down to the lack of an appropriate type definition in Bql for injection of an array of values. The regular Acumatica IN Operator uses a regular PX Select with a single DAC, and accepts an array of values set in C# for injection (see: SQL In Operator in BQL - Acumatica Developers Blog .....)

However, there is no mechanism in Bql to set an array of values to a Bql-typed variable at runtime that the IN operator would accept. Hugues and I went back and forth on this for quite a while...acumatica - How to use BQL In Operator with Select2 query / PXProjection and list of values - Stack Overflow

My ultimate solution was to create 100 Bql variables and use the OR operator. Since Bql operands are nullable at the database level (ORM handles this automagically), that was the insight I needed to figure this out; I realized this from doing a SQL trace.

If Acumatica wanted to fix this, they would just need to create some sort of BQL type that can mimick a typed array (ie array of strings, array of ints, depending on the field being used). Then just tweak IN to accept this array. Or create some mechanism to cast an array to a custom BQL type. There are a bunch of ways to do it.

 

Thanks for your feedback!

 

Jonathan

 


 

Userlevel 6
Badge +1

Hello Jonathan, 

I’ve checked the stackoverflow question and yeah, I see what you are trying to do here. 

So, creating a BQL type for a typed array sounds ok at first, but there are some complications:

  1. There is no SQL type we can match that BQL field to
  2. There is no attribute and no UI control for that field type

So, having a field type that cannot really be used anywhere is a questionable initiative.

 

However, if you still just want exactly that for your case, you can really just implement it yourself.

You’ll need to implement the BQL type and the attribute.

Here is the code:

//using System, PX.Data.BQL, PX.Data, PX.Data.SQLTree

// In4 to allow a single operand to be passed to In3
public class In4<Operand> : In3
where Operand : IBqlOperand
{
public In4() : base(typeof(Operand)) { }
}

// Bql type for string array
public abstract class BqlStringArray : BqlType<IBqlStringArray, string[]>
{ private BqlStringArray() { } }
public interface IBqlStringArray : IBqlDataType, IBqlComparable { }

// Attribute to set proper SQL type
[AttributeUsage(AttributeTargets.Property | AttributeTargets.Parameter | AttributeTargets.Class | AttributeTargets.Method)][PXAttributeFamily(typeof(PXFieldState))]
public class PXStringArrayAttribute : PXEventSubscriberAttribute, IPXFieldSelectingSubscriber, IPXCommandPreparingSubscriber
{
public PXStringArrayAttribute() { }

#region Implementation

// I don't actually think this event is required
public virtual void FieldSelecting(PXCache sender, PXFieldSelectingEventArgs e)
{
if (_AttributeLevel == PXAttributeLevel.Item || e.IsAltered)
{
e.ReturnState = PXStringState.CreateInstance(e.ReturnState, 100, true, _FieldName, false, null, null, null, null, null, null);
}
}

public virtual void CommandPreparing(PXCache sender, PXCommandPreparingEventArgs e)
{
if ((e.Operation & PXDBOperation.Command) == PXDBOperation.Select && e.Value != null)
{
e.BqlTable = _BqlTable;
if (e.Expr == null) e.Expr = new Column(_FieldName, _BqlTable);
//that's kind of hack here with DirectExpression type since there is no actual Array type in SQL
e.DataType = PXDbType.DirectExpression;
e.DataValue = e.Value;
}
}
#endregion
}

public class TestFilter : IBqlTable
{
#region FieldForIn4
public abstract class tranTypesArray : BqlStringArray.Field<tranTypesArray> { }

[PXDefault(new string[] {"Foo", "Bar"} )]
[PXStringArray]
public virtual String[] TranTypesArray
{
get; set;
}
#endregion

}
//put this in graph
public PXFilter<TestFilter> InTestFilter;
public PXSelect<CATran,
Where<CATran.origTranType, In4<Current<TestFilter.tranTypesArray>>>> TestSelect;

 

As for In3 accepting non constant values, I’ve just tested it and it does work (if there are more than one parameters). (For a single parameter you can use In4 from the example above.) (I’ve not checked with CurrentValue specifically though)

Here is the code:

public class TestFilter : IBqlTable
{
#region FieldForIn3
public abstract class tranType1 : PX.Data.BQL.BqlString.Field<tranType1> { }
[PXString(3)]
[PXDefault("Foo")]
public virtual String TranType1
{
get; set;
}
public abstract class tranType2 : PX.Data.BQL.BqlString.Field<tranType2> { }
[PXString(3)]
[PXDefault("Bar")]
public virtual String TranType2
{
get; set;
}
#endregion
}

public PXFilter<TestFilter> InTestFilter;
public PXSelect<CATran,
Where<CATran.origTranType, In3<
Current<TestFilter.tranType1>,
Current<TestFilter.tranType2>>>> TestResultsView;

 

Userlevel 6
Badge +2

Hi Dmitry-

Thanks for all the example code. Let me do some testing with what you’ve proposed and see how it goes. We’re still in PXSelect land (versus Select2, etc like in my original example), so let me see if there are any differences as a result of that.

I really appreciate the example of extending the Bql Operand with your In4 example.

I actually don’t agree that this is a hack… //that's kind of hack here with DirectExpression type since there is no actual Array type in SQL) I think that a single column table is actually the best proxy in SQL for a typed array. If you think about it, a common way to use the IN operator in native sql is with select * from mytable where myfield1 IN (select somefield from table2). So I think you are on the right track with that idea. For transient values that don’t live in an existing table, the database equivalent would probably be a single column table variable.

I’m not sure I buy that PXDefault counts as setting the values at runtime. I’m pretty sure I tried that in my code, and I think it might have worked (but setting with CurrentValue didn’t). However, I can’t 100% remember, so I will go back and put together an example to see what’s what.

Thanks for you help with this. I think we will find the answer with a little more work!

Jonathan

 

 

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2022  Acumatica, Inc. All rights reserved