How to select first record from joined table in select statement

Dynamics 365 for Finance and Operations/AX Developer Forum
1

When we open invoice journal from the subscription project
each invoice will have multiple records in subscription tab (ProjInvoicerevenue table)

We want to get some fields from the first record in ProjInvoicerevenue for each invoice and display in grid

For this we wrote a select statement to get first record from the joined table as below

invoiceJournal – is main table
ProjInvoicerevenue – is child table

select ProjInvoiceId
from invoiceJournal
join firstonly from ProjInvoicerevenue where ProjInvoicerevenue.ProjInvoiceId == invoiceJournal.ProjInvoiceId;

But we am getting all the records from the joined table, not only the first record.

we achieve this by using while select statement by, we are looking for single statement.

Can you please suggest with some idea…

Thanks,
Satish

2

Your code is not even compilable - please ensure yourself that you’re giving us the right code, otherwise it may be impossible to identify your problem.

If you have a query that doesn’t work as expected, check the resulting SQL statement. This is how you can do it in AX 2012:

ProjInvoiceJour invoiceJournal;
ProjInvoiceRevenue invoicerevenue;

select generateOnly ProjInvoiceId from invoiceJournal
    join firstonly RecId from invoiceRevenue
    where invoicerevenue.ProjInvoiceId == invoiceJournal.ProjInvoiceId;
    
info(invoiceJournal.getSQLStatement());

It returns the following:

SELECT T1.PROJINVOICEID,T1.RECID,T2.RECID FROM PROJINVOICEJOUR T1
CROSS JOIN PROJINVOICEREVENUE T2
WHERE ((T1.PARTITION=?) AND (T1.DATAAREAID=?))
AND (((T2.PARTITION=?) AND (T2.DATAAREAID=?))
AND (T2.PROJINVOICEID=T1.PROJINVOICEID))

As you can see, the firstOnly flag is completely ignored. You could use it to instruct the query to return the first record only, but it would have to be put on the top-level table. It has no effect on joined tables.