UNION and UNION ALL Operations in Traditional BQL

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

Performing a Union Operation in BQL

Suppose that you are 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 you want to perform a UNION operation by using the DACs that have been defined above. You need to declare a DAC that will store the result of the UNION operation. To do this, you 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, you 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. You 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>
{
}

You 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, you 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, you can execute the following BQL statement.

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

You use the MappedSelect command to facilitate the use of the Union and UnionAll operations in traditional BQL. 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

In the preceding code example, the first parameter of the MappedSelect command has specified the shared Storage DAC that will store the result of the UNION operation. The From command in the second parameter has specified the DACs (InternalStorageMapped and ExternalStorageMapped) on which the UNION operation will be performed. Finally, the Where and OrderBy clauses, respectively, have specified the filtering and ordering criteria for the operation. The following code shows the SQL equivalent of the BQL statement in the preceding code example.

SELECT [Storage].[StorageID],
       [Storage].[StorageCD],
       [Storage].[Active],
       (CASE
            WHEN ([Storage].[StorageType] = 1) THEN 'INTERNAL'
            WHEN ([Storage].[StorageType] = 2) THEN 'NAS'
            WHEN ([Storage].[StorageType] = 3) THEN 'CLOUD'
            ELSE 'UNKNOWN' END),
       [Storage].[StorageType]
FROM (SELECT [InternalStorage].[StorageID] AS [StorageID],
             [InternalStorage].[StorageCD] AS [StorageCD],
             [InternalStorage].[Active]    AS [Active],
             [InternalStorage].[TheType]   AS [StorageType]
      FROM [InternalStorage] [InternalStorage]
      WHERE ([InternalStorage].[CompanyID] = 2)
      UNION
      (SELECT [ExternalStorage].[StorageID]   AS [StorageID],
              [ExternalStorage].[StorageCD]   AS [StorageCD],
              [ExternalStorage].[Active]      AS [Active],
              [ExternalStorage].[StorageType] AS [StorageType]
       FROM [ExternalStorage] [ExternalStorage]
       WHERE ([ExternalStorage].[CompanyID] = 2))) [Storage]
WHERE ([Storage].[StorageID] > 1)
ORDER BY [Storage].[StorageID]

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