Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics AX (Archived)

convert SQL query to AX query

(0) ShareShare
ReportReport
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!

Tips for Writing Effective Verified Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,445 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans