How to Create Query

Finance and Operations/AX Developer Forum
1

Hi Master,

I want to create a Query in AX 2012, I have tried a query on SQL, I want to implement SQL code to Query AX 2012.

I have a problem, when I have made the query, the data should appear 4 lines, but when in View in AX 2012 the data show 12 lines.

How I can create query like code SQL below ? Please advice

Thanks

*Query SQL

querySQL.jpg
querySQL.jpg888×161 62.5 KB

*Properties in Query AX 2012 (what i have to change ?)

queryAX.jpg
queryAX.jpg746×347 81 KB

2

Yes, you can create such a query. Unfortunately I can’t comment on your problem, because I don’t have your data (therefore the information about number of lines is useless for me) and you didn’t show us the query you’ve generated. It’s also not clear why you think you should change the properties highlighted in last two images.

Here are two suggestions for your debugging:

  • You created a query that should look like the given SQL code, but it differs. Therefore look at what SQL code is generated for your query and compare it with the SQL code you intended to get.
  • You could simplify the query by removing everything that behaves the same. For example, if you find that the problem is in a particular join between two tables (rather than somewhere in a query with nine tables), identifying the exact cause will be much easier.
3

Hi Pranyoto,

the SQL query can be converted to AX query like this, please try with this.

select PurchId, ItemId from purchLine
        join purchTable
            where purchLine.PurchId == purchTable.PurchId && purchLine.PurchId == 'PO1700006006'
                join vendPackingslipTrans
                    where purchLine.InventTransId == vendPackingslipTrans.InventTransId
                        join vendPackingSlipJour
                            where vendPackingslipTrans.PackingSlipId == vendPackingSlipJour.PackingSlipId
                                join vendInvoicePackingSlipQuantityMatch
                                    where vendPackingslipTrans.SourceDocumentLine == vendInvoicePackingSlipQuantityMatch.PackingSlipInvoiceSourceDocumentLine
                                        join agitPobranchView
                                            where agitPobranchView.PurchId == purchLine.PurchId
                                                join agitPobussinesunitView
                                                    where agitPobussinesunitView.PurchId == purchLine.PurchId
                                                        join agitPochanelView
                                                            where agitPochanelView.PurchId == purchLine.PurchId;
4

I still insist on my advice. Instead of comparing SQL with X++, compare the SQL you get from your query with SQL code you wanted to get. And consider simplifying your query - you see that you have no idea what’s wrong if you’re trying to debug everything at once.
Use “generateOnly” keyword in your query (select generateOnly …) and than call purchLine.getSqlStatement() to get the actual SQL code for your select statement.

5

Hi Martin

I have tried compare my SQL code with Query AX 2012, but I’m still confused what’s the different between :

FetchMode : 1:1
Join Mode : Inner Join

FetchMode 1:n
Join Mode : Inner Join

FetchMode 1:1
Join Mode : Outer Join

FetchMode 1:n
Join Mode : Outer Join

6

Hi Thomas,

Thanks, but I mean how to create Query on AX 2012 like SQL code I have.

This’s Query from me, please, correct me if i wrong

queryAX1.jpg
queryAX1.jpg678×736 101 KB

7

That’s a completely different query than the SQL code above. For example, there was no VendTable, you miss the AGIT_* views, you don’t have any outer joins…

8

Hi Martin

I know, I just wanna “How to use Left Join to Query in AX ?” What should I use ? and what is the difference between options on below ? Can you advise

FetchMode : 1:1

Join Mode : Inner Join

FetchMode 1:n

Join Mode : Inner Join

FetchMode 1:1

Join Mode : Outer Join

FetchMode 1:n

Join Mode : Outer Join

Thanks

9

If you want an outer join, use JoinMode = OuterJoin and not JoinMode = InnerJoin.

10

I know, but what should I choose for fetchmode, 1:1 or 1:n ? after I chosen joinmode = OuterJoin.

11

Why don’t you simply try it? You should get the same result. Nevertheless using 1:1 will make easier to analyze the query string, if needed.