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
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…
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:
select generateOnly ProjInvoiceId from invoiceJournal
join firstonly RecId from invoiceRevenue
where invoicerevenue.ProjInvoiceId == invoiceJournal.ProjInvoiceId;
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=?))
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.