How to Create Query

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

  

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

 

  • 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.
  • 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;

  • In reply to ThomasAlvy:

    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.
  • In reply to Martin Dráb:

    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
  • In reply to ThomasAlvy:

    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 

     

  • In reply to Pranyoto:

    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...
  • In reply to Martin Dráb:

    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

  • In reply to Pranyoto:

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

  • In reply to Martin Dráb:

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

    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.
Related
Recommended