Finance | Project Operations, Human Resources, ...

How to integrate firstonly in parent query?

(1) Share

Report

Posted on by CU27040849-4

Hello, D365 experts.

I would like to integrate child query which start from select firstonly in parent query. Could you give me some advice?
Here is the code example.

while select

FieldA,
from

TableA
group by

TableA.FieldA
TableA.FieldB
where

TeamA.FieldA == TableB.FieldB
{
select firstonly

FieldC
from
TableB
where TableB.FieldD == /1/;

I have the same question (0)

All responses (4)

Answers (0)

Sort by

Suggested answer

Martin Dráb 239,855 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!

Your code is strange, because you run the query for FieldC for every record returned by the outer query, but there is no reason for it, because it doesn't depend on data data fetched there. It'll give you the same result every time, therefore running it just once (before the while select) will be more efficient.

By the way, when asking in the AX forum, please always mention your version of AX. If you actually mean D365 F&O, not Dynamics AX, use forums like Dynamics 365 Finance.

Was this reply helpful? Yes No
Suggested answer

Waed Ayyad 9,183 Super User 2026 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi,

As Martain said your select statement will return the same value so call it one time before the While loop. Can you tell us what you want to achieve exactly?
Also, I see here on your code you don't select the Table B, did you select it before the while?

where
TeamA.FieldA == TableB.FieldB

Thanks,

Waed Ayyad

If this helped, please mark it as "Verified" for others facing the same issue

Was this reply helpful? Yes No
CU27040849-4 20 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you Martin and Waed.
I am sorry for confusing by my code. I paste more detail here.
I would like to exclude second query from while loop in order to speed up process.

Table tableA;
Table tableB;
Table tableC;
Table tableD;

while select Field1,
Field2,
sum(Field3),
sum(Field4),
sum(Field5),
sum(Field6),
sum(Field7)
from tableB
group by tableB.Field1,
tableB.Field2
where tableB.Flag1 == value1
&& tableB.GroupId >= startNumber
&& tableB.GroupId <= endNumber
&& tableB.Flag2 != stopValue
&& tableB.Field1 == invoiceNumber
{
select firstonly Field8
from tableA
where tableA.Flag1 == value2
&& tableA.Field9 == value3
&& tableA.Flag2 == value4
&& tableA.Flag3 != stopValue
&& tableA.GroupId >= startNumber
&& tableA.GroupId <= endNumber
&& tableA.Flag1 == value1
&& tableA.Flag4 == value5
&& tableA.Field1 == invoiceNumber
&& tableA.Field2 == tableB.Field2;
{
Continue some processes.........
}

Was this reply helpful? Yes No
Suggested answer

Martin Dráb 239,855 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

You seem to be saying that the query for tableA can return multiple record and you want to take one of them - you don't care which one (your code say the first, but it doesn't specify any sorting). If you don't care which one you get, what about getting the lowest of the highest Field8? You can do that by joining tableA in the main query and adding an aggregation function (e.g. minOf(Field8)).

Was this reply helpful? Yes No