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) / Convert a Complex Sele...
Microsoft Dynamics AX (Archived)

Convert a Complex Select statement into a Query

(0) ShareShare
ReportReport
Posted on by HiteshAX 52

I need to convert the following select statement into a Query. The statement is a bit complex ,hence I am stuck. Can somebody kindly help in this regard ?

while select caseDetailBase1
join caseCategoryHierarchyDetailloc
where (caseDetailBase1.CategoryRecId == caseCategoryHierarchyDetailloc.RecId
&& (caseCategoryHierarchyDetailloc.LegalConfidential == NoYes::Yes
|| caseCategoryHierarchyDetailloc.LegalConfidential == NoYes::No)
&& caseDetailBase1.createdBy == curUserId())

|| (caseDetailBase1.CategoryRecId == caseCategoryHierarchyDetailloc.RecId
&& caseCategoryHierarchyDetailloc.LegalConfidential == NoYes::No
&& caseDetailBase1.createdBy != curUserId())

*This post is locked for comments

I have the same question (0)
  • HiteshAX Profile Picture
    HiteshAX 52 on at

    Hi Crispin,

    Thanks so much for the reply. Actually I need to add this query to the initializeQuery() method of a ListPage. Creating 2 different queries and stacking them one under the other sounds like a possible solution. But , would both the conditions work ? Actually the requirement is that I need to display only the nonConfidential records from other users along with all the records of the current user.

  • Suggested answer
    Malcolm Burtt Profile Picture
    Malcolm Burtt 1,475 on at

    Hi

    Based on the requirement you have described, it looks to me like you have over complicated the query. Distilling you described requirement, you want to display all non-Confidential records plus all records created by the current user. So I would have written the x++ select like this...

    while select caseDetailBase1

join caseCategoryHierarchyDetailloc

where caseDetailBase1.CategoryRecId == caseCategoryHierarchyDetailloc.RecId

&& (caseCategoryHierarchyDetailloc.LegalConfidential == NoYes::No

|| caseDetailBase1.createdBy == curUserId())

    You could implement that with a UNION query (one part returning all non-Confidential records and the other returning all Confidential records created by the current user but that's probably not the most efficient way as, I think, there is a way to directly implement the X++ code above using a single query.

    I'd look at implementing this with a complex query range - these can be tricky to get right as the syntax required is very precise but its worth persevering with. Have a look at this blog to get to grips with the approach...

    community.dynamics.com/.../how-to-use-complex-query-ranges-in-dynamics-ax

  • Verified answer
    HiteshAX Profile Picture
    HiteshAX 52 on at

    Thankyou so much Malcolm for simplifying the select statement. I used this link to convert select statement to query - "www.axaptapedia.com/Expressions_in_query_ranges". Your answer was very helpful.

  • HiteshAX Profile Picture
    HiteshAX 52 on at

    Thankyou so much Crispin, for the answer. I used this link to convert select statement to query - "www.axaptapedia.com/Expressions_in_query_ranges".

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
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans