You’re offline. This is a read only version of the page.
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
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
Thanks for the suggestion. Unfortunately, adding a computed column doesn't do what I need. I need a computed range instead.
Look at adding a computed column in your view. You can write as much SQL as you like, and then range on that column.
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.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,188
Super User 2024 Season 2
#2
Martin Dráb
230,030
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (