Solved

How can combine some heterogenous DACs (Equivalent to SQL UNION ALL)

  • 27 September 2022
  • 5 replies
  • 278 views

Userlevel 7
Badge +8

Hello Everyone,

I have a SQL View where I join some tables in each block and then, using UNION ALL, I combine all blocks and return a list. In Acumatica by using PXProjection, I should be able to replicate the result of each block as a flat DAC but I am not sure how then I can aggregate these Projections to get a unionized result of all DACs. As a very simple example, how I can replicate the below SQL example in Acumatica?

CREATE VIEW [MyUnionView] AS
SELECT 'AP' AS [DocType], [RefNbr] AS [RefNbr] FROM [APInvoice]
UNION ALL
SELECT 'PO' AS [DocType], [OrderNbr] AS [RefNbr] FROM [POOrder]
UNION ALL
SELECT 'PM' AS [DocType], [ContractCD] AS [RefNbr] FROM [Contract]
GO

SELECT [DocType], [RefNbr] FROM [MyUnionView]
GO

 

icon

Best answer by Leonardo Justiniano 28 September 2022, 01:14

View original

5 replies

Userlevel 6
Badge +4

Hi @aaghaei 

You have basically two options:

  1. You create a SQL View as you described, and then a DAC MyUnionView class to map its records .
  2. You create a Acumatica view delegate where you can execute your BQL queries and then, by looping, build the final result set of the queries:

Example:

PXSelect<DAC> Data;

...

public IEnumerable data()
{

// BQL queries & looping

return listData;

}

BQL does not provide the UNION mechanism yet. Therefore, you won’t be able to create a projection to define an UNION statement like in SQL. Projections are translated to queries when they are processed on the screen call:

PXProjection(typeof(BQL))]
public class DAC_Projection : IBqlTable
{
// Fields mapping original DAC
}

translates into

SELECT ...
FROM (
SELECT ... FROM ORIGINAL_DAC
) DAC_Projection

Hope this help

Userlevel 7
Badge +8

Thanks @Leonardo Justiniano 

i already have implemented the SQL View base approach.

Actually I was thinking about the view delegates as well but I think the join tables are processed in delegate should have one common DAC which is not in my case. I see this from T250 course that I just watched a few days ago. Any thoughts.

Userlevel 6
Badge +4

Hi @aaghaei 

 

Thinking about delegates, I would create a generic unbound DAC similar to the one used for the SQL View. The view then is based on that class and you can return a list by querying all sources you want to UNION during the delegate’s processing.

On the T250 course, the projections part, you define the class mapping each IBqlField from the projection’s BQL query classes.

// FROM T250


[PXCacheName("Invoice and Payment of the Repair Work Order")]
[PXProjection(typeof(SelectFrom<PX.Objects.AR.ARInvoice>.
InnerJoin<ARAdjust>.On<ARAdjust.adjdRefNbr.IsEqual<ARInvoice.refNbr>.
And<ARAdjust.adjdDocType.IsEqual<ARInvoice.docType>>>.
AggregateTo<
Max<ARAdjust.adjgDocDate>,
GroupBy<ARAdjust.adjdRefNbr>,
GroupBy<ARAdjust.adjdDocType>>))]
public class RSSVWorkOrderPayment : IBqlTable
{
....

#region AdjgRefNbr
[PXDBString(BqlField = typeof(ARAdjust.adjgRefNbr))] // <-- Mapping ARAdjust from Join
[PXUIField(DisplayName = "Latest Payment", Enabled = false)]
public virtual String AdjgRefNbr { get; set; }
public abstract class adjgRefNbr : PX.Data.BQL.BqlString.Field<adjgRefNbr> { }
#endregion

....
}

But, you can’t have different sources like in an UNION as BQL only support queries from one source (Multiple joined tables yes but not multiple BQLs). 

Then when creating a view from RSSVWorkOrderPayment the projection BQL will be processed as subquery. 

Userlevel 7
Badge +8

Thanks @Leonardo Justiniano 

 

what I have developed based on SQL view right now works perfectly even without delegate. I want to remove the view I have created in SQL and implement everything in the code to make my project independent from DB type.

 

what is similar to my case from T250 is where SO and WO are combined but they have the RSSVWorkOrder in common but I do not have common table in my union blocks.

Userlevel 6
Badge +4

Hi @aaghaei 

The training is implementing the delegate in the same way I explained at first:

 // Converts the shipment to a WO to pay
RSSVWorkOrderToPay workOrder = RSSVWorkOrderToPay(soshipment);

workOrder.OrderType = OrderTypeConstants.SalesOrder;
var result = new PXResult<RSSVWorkOrderToPay, ARInvoice>(
workOrder, invoice);
yield return result;

In the end you are compiling all soshipments into RSSVWorkOrderToPay DAC collection record. 

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 — 2024  Acumatica, Inc. All rights reserved