web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Subquery in AX View
Microsoft Dynamics AX (Archived)

Subquery in AX View

(0) ShareShare
ReportReport
Posted on by Brad Docimo 485

AX allows you to enter basic SQL into View ranges.  For example, in an AOT view's range, for the match value, you could enter (StatRepInterval.Name == 'Weekly').  This works nicely.

However, I need to do a more advanced lookup on a View, using a subquery.  Can anyone suggest a way to do this? 

This is what I would like to use, but I receive an error: "Query extended range failure: Syntax error near 34."

(StatRepInterval.Name == (SELECT FIRSTONLY StatRepInterval.Name FROM StatRepInterval WHERE StatRepInterval.PrintDirection == 1 ORDER BY StatRepInterval.Name DESC))

I've tried a lot of different variants of the subquery, from straight T-SQL to X++ SQL, but nothing seems to work. 

Thanks for the help.

*This post is locked for comments

I have the same question (0)
  • Verified answer
    Brad Docimo Profile Picture
    Brad Docimo 485 on at

    I found a way to do this. It isn't pretty, and I'm going to leave the question unanswered for a bit, should someone else have a more graceful solution.

    Create a source View that contains all fields I wish to return, plus calculated fields that contain my subquery results.

    Create a second View that uses the first as a data source, and applies all the necessary ranges.

    Works pretty nicely.

    Probably inefficient if there were large tables of data, but this is in a relatively small section of AX.

  • Suggested answer
    ColbyGallagher Profile Picture
    ColbyGallagher 3,666 on at

    Look at adding a computed column in your view.  You can write as much SQL as you like, and then range on that column.      

    msdn.microsoft.com/.../gg845841.aspx

  • Brad Docimo Profile Picture
    Brad Docimo 485 on at

    Thanks for the suggestion.  Unfortunately, adding a computed column doesn't do what I need.  I need a computed range instead.

  • Suggested answer
    Koen Roks Profile Picture
    Koen Roks on at

    When I have a situation where I need a subquery in AX I normally join on the same table in most cases with an exists / not exists join

    Pseudo code

    select StatRepInterval exists join StatRepInterval2
    order by StatRepInterVal2 desc
    where StatRepInterval.RecId = StatRepInterval2.RecId
    && StatRepInterval2.PrintDirection == 1

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Priya_K Profile Picture

Priya_K 4

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#3
Scott_itD Profile Picture

Scott_itD 2 Community Manager

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans