How to create query within a query in AOT?

Finance and Operations/AX Developer Forum
1

Hi, I’m having a problem creating queries in AOT, is it possible to create a query within a query using AOT?

here’s my sql sample code, and I don’t have any idea how to create this in AOT.

SELECT d.ITEMID, c.NAME, CASE WHEN LEVEL_ = 2 or LEVEL_ = 1 Then d.NAME else ‘’ END Category,

CASE WHEN LEVEL_ = 3 THEN d.NAME else ‘’ end SubCategory,

c.INVOICEDATE, c.INVOICEID,j.SALESNAME, c.QTY , c.LINEAMOUNT, c.LINEAMOUNT,

INVENTSERIALID, STATUSISSUE

from CUSTINVOICETRANS c

INNER JOIN

(select a.ITEMID, a.INVOICEID,INVENTSERIALID, b.ITEMNAMEALIAS, STATUSISSUE, LEVEL_, i.NAME from INVENTTRANS a

INNER JOIN INVENTDIM b ON

2

Dynamics AX doesn’t support subqueries. Either rewrite your query with normal joins, model the subquery as a view and use the view in your query, or use some other workaround.

(Question moved from the End User forum.)

3

thanks for replying sir, How about using ReportDataProvider? can RDP create this?

4

No, it’s a completely unrelated topic. Report data provider is an X++ class called by SSRS reports to prepare data for them. It can prepare the data in absolutely any way, it doesn’t have to even touch database at all. It can use queries, but it’s not a way for writing them.

But it suggest that you probably asked a wrong question. Maybe you thought so much about one particular way to implement your goal that you forgot to look for other ways. Please forget implementation details for a moment and tell us what you’re trying to achieve from functional perspective.

Maybe your attempt to fetch all data in a single query is an artificial requirement created by yourself. If not, please don’t ignore the suggestions I have you in my previous reply.