Platform API: Introduction of the UNION and UNION ALL SQL Database Operations in BQL

Starting with MYOB Acumatica2024.2.1, the MYOB Acumatica Framework now natively supports the standard UNION and UNION ALL SQL database operations in BQL. Developers can now write BQL queries that support these operations by using the Union and UnionAll keywords in their BQL statements.

Performing a Union Operation in BQL

Suppose that a developer is working with two data access classes (DACs) called ExternalStorage and InternalStorage. These DACs are shown in the following code.

public class ExternalStorage : PXBqlTable, IBqlTable
{
   #region StorageID
   [PXDBInt(IsKey = true)]
   public virtual Int32? StorageID { get; set; }
   public abstract class storageID : BqlInt.Field<storageID> { }
   #endregion

   #region StorageCD
   [PXDBString]
   public string StorageCD { get; set; }
   public abstract class storageCD : BqlString.Field<storageCD> { }
   #endregion

   #region Active
   [PXDBBool]
   public virtual Boolean? Active { get; set; }
   public abstract class active : BqlBool.Field<active> { }
   #endregion

   #region StorageType
   [PXDBInt]
   public virtual int? StorageType { get; set; }
   public abstract class storageType : BqlInt.Field<storageType> { }
   #endregion
}

public class InternalStorage : PXBqlTable, IBqlTable
{
   #region StorageID
   [PXDBInt(IsKey = true)]
   public virtual Int32? StorageID { get; set; }
   public abstract class storageID : BqlInt.Field<storageID> { }
   #endregion

   #region StorageCD
   [PXDBString]
   public string StorageCD { get; set; }
   public abstract class storageCD : BqlString.Field<storageCD> { }
   #endregion

   #region Active
   [PXDBBool]
   public virtual Boolean? Active { get; set; }
   public abstract class active : BqlBool.Field<active> { }
   #endregion

   #region TheType
   [PXDBCalced(typeof(int_1), typeof(int))]
   public virtual int? TheType { get; set; }
   public abstract class theType : BqlInt.Field<theType> { }
   #endregion
}

Further suppose that the developer wants to perform a UNION operation by using the DACs above. The developer needs to declare a DAC that will store the result of the UNION operation. To do this, the developer can declare a shared DAC called Storage as follows.

public class Storage : PXBqlTable, IBqlTable
{
   #region StorageID
   [PXDBInt(IsKey = true)]
   public virtual Int32? StorageID { get; set; }
   public abstract class storageID : BqlInt.Field<storageID> { }
   #endregion

   #region StorageCD
   [PXDBString]
   public string StorageCD { get; set; }
   public abstract class storageCD : BqlString.Field<storageCD> { }
   #endregion

   #region Active
   [PXDBBool]
   public virtual Boolean? Active { get; set; }
   public abstract class active : BqlBool.Field<active> { }
   #endregion

   #region StorageTypeCD
   [PXDBCalced(typeof(Switch<Case<Where<storageType, Equal<int_1>>, 
                 string_Int, Case<Where<storageType, Equal<int_2>>, 
                 string_Nas, Case<Where<storageType, Equal<int_3>>,
                 string_Clo>>>, string_Unk>), typeof(string))]
   public virtual string StorageTypeCD { get; set; }
   public abstract class storageTypeCD : BqlInt.Field<storageTypeCD> { }
   #endregion

   #region StorageType
   [PXDBInt]
   public virtual Int32? StorageType { get; set; }
   public abstract class storageType : BqlInt.Field<storageType> { }
   #endregion

}

Before performing the UNION operation on the ExternalStorage and InternalStorage DACs and storing the resulting data in the shared Storage DAC, the developer must define the relationship between the fields of the ExternalStorage and InternalStorage DACs and the fields of the shared Storage DAC in the relevant graph. A developer can do this by using the BqlTableMapper class, as shown in the following code.

public class InternalStorageMapped : BqlTableMapper<InternalStorage, Storage>
{
    public InternalStorageMapped()
    {
        Map<Storage.storageType.EqualTo<InternalStorage.theType>>();
    }
}

public class ExternalStorageMapped : BqlTableMapper<ExternalStorage, Storage>
{
}

A developer can also use the BqlFieldMapper class, which maps the fields of the DACs involved in the UNION operation with the shared DAC. By default, the BqlFieldMapper class maps these fields based on their names. However, a developer can override this mapping in a number of ways, as shown in the following code.

public class ExternalStorageToStorage : BqlFieldMapper<ExternalStorage, Storage>
{
    public ExternalStorageToStorage()
    {          
        Map<Storage.storageType.EqualTo<ExternalStorage.theType>>();
        Map<Storage.storageID.EqualTo<const_int_1>>();
        Map<Storage.storageCD.EqualTo<ConvertToStr<ExternalStorage.storageID>>>();
        Map<Storage.active.EqualTo<ConvertToBool<DateDiff<PXDateAndTimeAttribute.now, 
         PXDateAndTimeAttribute.now, DatePart.day>>>>();
    }
}

Finally, to perform the UNION operation by using the Union keyword, the developer can execute the following BQL statement.

MappedSelect<Storage, From<InternalStorageMapped, Union<ExternalStorageMapped>>,
 Where<Storage.storageID, Greater<int_1>>, OrderBy<Asc<Storage.storageID>>>

To facilitate the use of the Union and UnionAll operations in BQL, a new type of BQL select command called MappedSelect has been introduced, as shown in the code above. This command is used to specify the following:

• How the union operation will be performed on a set of DACs representing the relevant database tables
• Which shared DAC the data of this operation will be stored in

To perform a UNION ALL operation by using the UnionAll keyword, the developer can perform steps that are similar to the ones described in this section, and exclude the filtering criteria from their BQL statement, if necessary.