Query in computed column

(0) Share

Report

Posted on by Walid Gamal

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

I have the same question (0)

All responses (4)

Answers (0)

Brandon Ahmad 2,465 User Group Leader on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Walid Gamal on at

Like (0)

Report

Hi Brandon

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

Regards.

Was this reply helpful? Yes No
Suggested answer

Brandon Ahmad 2,465 User Group Leader on at

Like (1)

Report

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.

Was this reply helpful? Yes No
Suggested answer

Martin Dráb 237,795 Most Valuable Professional on at

Like (0)

Report

AX2012: Subqueries in views might help you.

Was this reply helpful? Yes No