Union Selects in BQL

Hi Everyone,

Sometimes you want to select and combine data from different tables in database. There is no standard way to do it in BQL, but we can go with several workarounds:

  • Create a SQL server view. In this case you just create a view on SQL Server side with selecting, calculating and combining all data into one big select. After this you can create/generate a DAC that will be associated to the view. In the code you can easily use new data access class as you wish. System will always generate selects to SQL server view as it is normal table.
  • Use data view delegate, where you can execute several selects to database. When you have received data you can calculate and combine data with programming code as you wish. This way can be slower on big amount of data, but also here you have more control.

Here I want to describe in details the second way to do it.

What is delegate
Delegate is a special method that is associated to the DataView.

public class SomeMaint : PXGraph<SomeMaint>
{
    public PXSelect<UnionTable>

UnionSelect

;
    protected IEnumerable unionSelect()
    {
        .....
    }
}

It always has the same name as DataView, differs only in casing of the first letter. System will execute delegate instead of generating SQL query to the database server. This can be very helpfull, if you need:

  • Modify select query dynamically with Where<>,Join<>, …
  • You want to perform additional calculations and don’t want/can’t do it declaratively
  • You want to return ‘fake’ data records, which are not stored in the database, but are constructed in code.

Here is schema, how system will handle the delegate.

Acumatica BQL Select Architecture

How to do Union Dynamically

In the delegate we can do whatever we want, including selecting database, so we need to select data from 2 different tables and combine it into one result. Data View have to return only one DAC class as a result, so we should create a definition for virtual DAC, that will represents the combined union data.

Here I provide you a code example:

Of course, you have to understand that it is not completed code snippet. For example you need to aware about duplicate keys. May be it will be good idea, to use dictionary instead of list and check that you returning only unique data.

Have a nice Development!

5 Replies to “Union Selects in BQL”

  1. Hi Sergey,

    I want to replace my Database View into Projection DAC.
    But I am using UNION in my Database View.
    I am using Projection DAC for Reports (not using graph)
    How can i change my database view into Projection DAC ?
    Please explain.

    Thanks & Regards,
    Gaurav Katiyar

    1. Gaurav. Unfortunately I don’t have a positive answer for you. Projections do not support unions. So I would say it is not possible with projection.

  2. Hi Sergey,
    The "union" in the view delegate works fine.
    But there is a problem with paging, if the grid control has paging.
    The PXView.StartRow should be reset in delegate otherwise some records will be skipped.

    For example
    PXView.StartRow = 0
    PXView.MaximumRows = 11
    Assume 5 records from first table and 5 records from the second table were loaded into first page.

    When user clicks next page, PXView.StartRow will be set to 10 by framework,
    therefore 5 records from the first table will be skipped. It should be set to 6.

  3. Thanks Sergey, even though you posted this in 2015 and we still don't have a Union in BQL yet. I will suggest Acumatica they add this feature to BQL.

Leave a Reply

Your email address will not be published. Required fields are marked *