BQL Union notes

I had a Table Value function that used UNION ALL to merge the results of three queries. Then I wrote a Table Value function to return the results in sorted order. Finally, I added a DAC to sit on top of the SQL view.

Sometimes, after publishing, the above solution wouldn’t work unless we also restarted the application. That’s not a big deal to do after publishing but if we forgot, the GI would show errors.

So I switched out the above with a series of PXProjections and then a MappedSelect for the final result.

Some notes for future me:

Create each ‘view’ or sub-select as a PXProjection. This is a good time to decide if I’m going to make all the field names the name now, or if I’m going to map them together later.

PXDBCalced fields seem to benefit from using a ‘persisted’ field attribute instead of the suggested non-persisted attribute (PXDBInt instead of PXInt)

[Serializable]
[PXCacheName("Activity - Freight")]
[PXProjection(typeof(
  SelectFrom<ARRegister>
  .InnerJoin<ARInvoice>
    .On<ARInvoice.docType.IsEqual<ARRegister.docType>
    .And<ARInvoice.refNbr.IsEqual<ARRegister.refNbr>>>
  .InnerJoin<SOFreightDetail>
    .On<SOFreightDetail.docType.IsEqual<ARRegister.docType>
    .And<SOFreightDetail.refNbr.IsEqual<ARRegister.refNbr>>>
  .LeftJoin<BAccount>.On<BAccount.bAccountID.IsEqual<ARRegister.customerID>>
  .LeftJoin<ShipTerms>.On<ShipTerms.shipTermsID.IsEqual<SOFreightDetail.shipTermsID>>
  .Where<SOFreightDetail.curyFreightAmt.IsNotEqual<decimal0>>
  ), Persistent = false)]
public class ActivityByLocationFreight : PXBqlTable, IBqlTable
{

  #region CustomerID
  [PXDBInt]
  [PXUIField(DisplayName = "Customer ID", Enabled = false)]
  [PXDBCalced(typeof(IsNull<BAccount.parentBAccountID, ARInvoice.customerID>), typeof(int))]
  public virtual int? CustomerID { get; set; }
  public abstract class customerID : PX.Data.BQL.BqlInt.Field<customerID> { }
  #endregion

...

In the code for the final projection, I added BqlFieldMapper code (below). I have three - one for each view mapping into ActivityByLocationFinal.

public class BackOrderMapped : BqlFieldMapper<ActivityByLocationBackOrder, ActivityByLocationFinal> 
{

  public BackOrderMapped() 
  { 
    Map<ActivityByLocationFinal.parentBAccountID
      .EqualTo<ActivityByLocationBackOrder.customerID>>();
    Map<ActivityByLocationFinal.bAccountID
      .EqualTo<ActivityByLocationBackOrder.bAccountID>>();
    Map<ActivityByLocationFinal.salesOrderDate
      .EqualTo<ActivityByLocationBackOrder.salesOrderDate>>();
    Map<ActivityByLocationFinal.docType
      .EqualTo<ActivityByLocationBackOrder.docType>>();
    Map<ActivityByLocationFinal.docNbr
      .EqualTo<ActivityByLocationBackOrder.docNbr>>();
  }

}

Then, for the final projection, I call MappedSelect<>:

[Serializable]
[PXCacheName("ActivityByLocationView")]
[PXProjection(typeof(
  MappedSelect<ActivityByLocationFinal,
    From<BqlTableMapper<ActivityByLocationBackOrder, BackOrderMapped>
    ,Union<BqlTableMapper<ActivityByLocationShipment, ShipmentMapped>
    ,Union<BqlTableMapper<ActivityByLocationFreight, FreightMapped>
    >>>
    ,Where<ActivityByLocationFinal.docNbr.IsNotNull>>
 ))]

I didn’t map all of the fields but I think by mapping what I did, I prevented the DatabaseRecordStatus field from appearing in my query uninvited.

Final note (for now). When I tried to navigate to the ‘next’ page of records in the GI, I hit a SQL syntax error related to OFFSET. The problem was that the generated SQL did not have an ORDER BY clause and OFFSET requires it. Adding ,OrderBy<> to the final projection did not solve that issue. Adding fields to the GI’s Sort Order tab did fix that problem.

Adding OrderBy to the projection might work when used outside of a GI and the GI was simply overriding the OrderBy because it can.