Skip to main content

Notifications

Microsoft Dynamics AX (Archived)

Query in computed column

(0) ShareShare
ReportReport
Posted on by

Hi All

I Read about Computed Column and understood that it's used to do a calculation and have the calculation result as a field in the view.

However, i need to perform a query and return a result as a field in the view. the query that want perform can not be placed as a join in my view.

Can this be achieved using Computed Column ??

Regards.

*This post is locked for comments

  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 230,842 Most Valuable Professional on at
    RE: Computed Column

    AX2012: Subqueries in views might help you.

  • Suggested answer
    Brandon Ahmad Profile Picture
    Brandon Ahmad 2,465 User Group Leader on at
    RE: Computed Column

    Remembering what I said about this particular technique really jacking with performance if the view is of any reasonable size, I'll explain how to do it here.  You have to go outside of the documentation and get your advanced developer on a little bit, but here is how to do it.  Get the definition of your view in SQL Server by right clicking on the view and clicking "View Definition" from management studio.  

    Now, alter your view by adding a subquery within a case expression like here:

    blogs.msdn.microsoft.com/.../subqueries-in-case-expressions

    Make sure that it runs in sql -- that is how you know that it works.  Then copy the syntax only for the case statement with the subquery inside of it.  Create a computed column and put it in the '' (single quotes).  A computed column only expects a string that can fit within a sql statement.  You need to build a string that equals the exact expression in the sql statement that you tested earlier.  You'll know it works because the view will synchronize.  If it doesn't synchronize, it will warn you with the error and you can make changes.  

    Sadly, there is no documentation with an example like this (there are several simpler examples documented though) but I've done it a few times.  Just remember the key is that the string generated is the same that you tried out with direct sql when you tested the view.  

  • Walid Gamal Profile Picture
    Walid Gamal on at
    RE: Computed Column

    Hi Brandon

    Can u give me link for that or an example to understand it

    Regards.

  • Brandon Ahmad Profile Picture
    Brandon Ahmad 2,465 User Group Leader on at
    RE: Computed Column

    Yes, if you write your computed column as a subquery that equivocates to an expression, but be careful.  This often causes bad performance issues if the view is of any reasonable size.  It's almost always better to use a temp table when faced with this scenario.  

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

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,969 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 230,842 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans