How Acumatica treats five inner joins of classes which are relatives

Question

In my Graph I have following Inner joins chain:

    public PXSelectJoin<APRegister,
        InnerJoin<APPayment, On<APPayment.docType, Equal<APRegister.docType>>,
            InnerJoin<CATran, On<CATran.origTranType, Equal<APRegister.docType>>,
                InnerJoin<Vendor, On<Vendor.bAccountID, Equal<APRegister.vendorID>>,
                    InnerJoin<BAccount, On<BAccount.bAccountID, Equal<BAccount.bAccountID>>,
                        InnerJoin<VendorClass, On<VendorClass.vendorClassID, Equal<Vendor.vendorClassID>>>>>>>> ReleasedPayments;

and I'm particularly interested how Acumatica framework "feels" and maybe behaves about inner joins between classes that are relatives. For example APRegister is basic class to APPayment, and BAccount is basic class to Vendor. Will Acumatica framework be able to handle them properly? I give this question because mentioned query caused my processor to be over loaded, memory exhausted and then crashing w3wp process. So I've get an impression that something is missing

Back in the Acumatica world? Glad to see you around here again! — Gabriel, Feb 16 '17 at 01:26

score 1 · Answer 1 · answered Sep 28 '17 at 18:09

I noticed you were joining BAccount.bAccountID to itself. You likely meant to join to Vendor. Since BAccount is only joined to itself and not the other tables in any way, it might be acting like a Cartesian join as it would if there were no join condition specified. If you truly would like to join BAccount.bAccountID to itself for some reason, you'll likely want to use BAccountR or BAccount2 -- which are subclasses of BAccount -- or subclass it yourself. This should then use a different alias within the generated SQL statement and produce expected results.

Note: You might possibly not even need a BAccount join to Vendor. I discovered that a select from BAccount already includes an inner join to Vendor by default. The subclass BAccountR does not, though.

score 0 · Answer 2 · edited May 23 '17 at 11:46

0

My thought is it should work as long as the joins are correct. I remember having issues when trying to do a join on the same table using the same table multiple times. As long as the class name for BQL was different it worked just fine. Without testing it you really will not know for sure.

In reference to two of the same tables in a join: Acumatica BQL Query with the same table more than once

edited May 23 '17 at 11:46

Community

1
1

answered Feb 15 '17 at 21:44

Brendan

5,428
2
17
33

I give this question because mentioned query caused my processor to be over loaded, memory exhausted and then crashing w3wp process. So I've get an impression that something is missing – Yuriy Zaletskyy Feb 15 '17 at 21:47
1

Also, I would put your comment about the processing spike in your initial question as it helps to support why you are asking the question. – Brendan Feb 15 '17 at 22:01

How Acumatica treats five inner joins of classes which are relatives

2 Answers2