Skip to main content

Notifications

Microsoft Dynamics AX (Archived)

convert SQL query to AX query

Posted on by 11,873

I need to convert the SQL query below into AX query

select * from  PURCHRFQLINE  a  inner join PURCHRFQTABLE b on

a.RFQID=b.RFQID

inner join PURCHRFQREPLYLINE c

on c.RFQLINERECID=a.RECID

full outer join PURCHLINE d on d.Itemid=a.Itemid AND a.PURCHID = d.PURCHID

*This post is locked for comments

  • maram fraij Profile Picture
    maram fraij 11,873 on at
    RE: convert SQL query to AX query

    I need to build a query

  • Suggested answer
    Denis Macchinetti Profile Picture
    Denis Macchinetti 16,444 on at
    RE: convert SQL query to AX query

    Hi Guys

    From my side, the above query work well.

    I have also tested on my environment.

    In this case we don't have a combination of inner join and outer join on the same source as write at the link bmdax.blogspot.nl/.../joins-inner-outer-combined.html

    Regards

  • refa Profile Picture
    refa on at
    RE: convert SQL query to AX query

    try to find an alternative query for FULL OUTER JOIN, combination of outer joins, unions

    but i suppose it will not work in AX...

  • Suggested answer
    Dick Wenning Profile Picture
    Dick Wenning 8,705 on at
    RE: convert SQL query to AX query

    please check

    bmdax.blogspot.nl/.../joins-inner-outer-combined.html

    there are some issues

  • Suggested answer
    Denis Macchinetti Profile Picture
    Denis Macchinetti 16,444 on at
    RE: convert SQL query to AX query

    Hi Maram

    Could be something like this :

       PURCHRFQLINE        PURCHRFQLINE;

       PURCHRFQTABLE       PURCHRFQTABLE;

       PURCHRFQREPLYLINE   PURCHRFQREPLYLINE;

       PURCHLINE           PURCHLINE;

       ;

       while select PURCHRFQLINE  

       join PURCHRFQTABLE

       where PURCHRFQLINE.RFQId == PURCHRFQTABLE.RFQId

       join PURCHRFQREPLYLINE

       Where PURCHRFQREPLYLINE.RFQLINERECID == PURCHRFQLINE.RECID

       outer join PURCHLINE

       Where PURCHLINE.Itemid     == PURCHRFQLINE.Itemid

       &&    PURCHRFQLINE.PURCHID == PURCHLINE.PURCHID

           info (PURCHRFQLINE.RFQId);

    Regards

  • maram fraij Profile Picture
    maram fraij 11,873 on at
    RE: convert SQL query to AX query

    I need to create it firstly by code similar to the first example in the link for testing purposes then I will convert it to AOT query using the wizard

  • Denis Macchinetti Profile Picture
    Denis Macchinetti 16,444 on at
    RE: convert SQL query to AX query

    Hi Maram

    AX query mean AOT query or using query objects like the examples on the link below ?

    msdn.microsoft.com/.../aa638454.aspx

    Regards

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,240 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,149 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans
Liquid error: parsing "/globalsearch/?q=box+to++ni++and++all++of++them++to++y+and+and+and+and+and+and+and+and+and+and+and+and+and+and+and+and+and+OU+" - Nested quantifier +.