[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

COMPUTED COLUMN SQL

Posted on 2011-10-13
9
Medium Priority
?
197 Views
Last Modified: 2012-05-12
Hi All,

I would like to know about computed column.

I try below code but failed :

 "TotalLeftLeg SmallMoney DEFAULT 0," & _
                        "TotalRightLeg SmallMoney DEFAULT 0," & _
                        "TotalPaidLeftLeg (CASE WHEN TotalLeftLeg < TotalRightLeg THEN TotalLeftLeg ELSE 0 END)," & _
                        "TotalPaidRightLeg (CASE WHEN TotalRightLeg < TotalLeftLeg THEN TotalRightLeg ELSE 0 END)," & _

What's wrong with TotalPaidLeftLeg  and TotalPaidRightLeg ?

Thank you.
0
Comment
Question by:emi_sastra
  • 4
  • 4
9 Comments
 
LVL 9

Expert Comment

by:sachinpatil10d
ID: 36962191
can you please post the complete query
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 36962217
The problem is at :

TotalPaidLeftLeg (CASE WHEN TotalLeftLeg < TotalRightLeg THEN TotalLeftLeg ELSE 0 END)

Thank you.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36962570
What is the error message you get?
You can try:
TotalPaidLeftLeg AS (CASE WHEN TotalLeftLeg < TotalRightLeg THEN TotalLeftLeg ELSE 0 END)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:emi_sastra
ID: 36962595
Hi mwvisa1,

Yes, it is.

Would you please tell what computed column can do other than the above ?

Thank you.

0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 36962684
"Yes, it is." < Does that mean adding AS worked. Wonderful!
You can have this do a number of different expressions. For example, if you have a product family like BUSomeCode where the first two characters identify your business unit, then you can have a computed column that is BusinessUnit AS LEFT(ProductFamily, 2).

BOL: http://msdn.microsoft.com/en-us/library/ms191250.aspx
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 36962788
I meant it works.

The link tell about :

1. Math Function.
2. String Manipulation.
3. DateAdd Function.
4. Criteria like CASE above.

Is there any other usage ?
Is there some samples about it ?

Thank you.
 
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36963102
That probably covers most scenarios, but as stated in the documentation:
The expression can be a noncomputed column name, constant, function, and any combination of these connected by one or more operators. The expression cannot be a subquery.

Pinal Dave, SQL Server MVP, has some examples on his blog:
http://blog.sqlauthority.com/2010/08/03/sql-server-computed-column-persisted-and%C2%A0performance/

Hope that helps!
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36963115
The articles are also helpful for understanding performance and persistence. He has links to several different blog entries that each show different samples of computed columns.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 36965920
Ok.

Thank you very much for your help.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question