I have three X++ queries and I want to join all three of them with full outer join. They all have a field CustGroup so I want to use it to join them. I know that full outer join functionality isn’t supported in AX but I’m looking for a workaround. Before you ask me, they need to be X++ queries, I can’t use AOT queries in this case. So is there any way to do this?
I forgot to say (probably it’s obvious but still…), I want to show the result of this join in the form.
Can you tell us more about your specific case? It’s difficult to suggest a workaround suitable for all possible situations.
Thank you, I’ll be specific.
So I have this AOT Query:
SELECT SUM(Amount) FROM A GROUP BY F.Group, E.EId
JOIN FROM B ON A.B = B.RecId
JOIN FROM D ON B.SubV = D.Voucher
JOIN Group FROM F ON D.Account = F.Account
JOIN FROM E ON B.RecId = E.B
JOIN FROM C ON A.LedgerDimension = C.RecId
JOIN EId FROM E ON C.E = E.RecId
When the form X opens, user sees dialog in which he can enter three values: fromDate, toDate, E.
After the dialog closes I use the entered values to change the AOT query. I’m creating three new queries using the above mentioned AOT query and user’s values, I’m adding ranges to the existing AOT query:
Query1:
A.Amount > 0,
E.EId = E,
B.Date IS BETWEEN fromDate and toDate
Query2:
A.Amount < 0,
E.EId = E,
B.Date IS BETWEEN fromDate and toDate
Query3:
E.EId = E,
B.Date < fromDate
I want to join these three queries with F.Group field. All three work correctly, I just need to find a way to show them together.
If you have three queries and you want to combine their results, it sounds more like a task for UNION, not for FULL OUTER JOIN.
I don’t think so. But now I got an idea. I think I need to join them (1-2, 2-3. 1-3) and then use the Union.