I'm attempting to write the formula for a computed column in SQL Server 2005 using SQL Server Management Studio 2008. I get the error validating formula and I'm not sure how to write the formula to work.

If it were standard math the formula would be:
(mmtot * 22) + (mattot * 2) + odctot

mmtot, mattot, and odctot are also computed columns so that may be the problem but I'm not sure.

Hi,

you should give some more details, especially on the obviously wrong select statement containing those computed columns. If mmtot, mattot, and odctot are also computed columns, what they are computed from, from real table columns?

Simple post your select statement and some information on the involved tables too.

-- As for example

select (a+ b) * c as x, x + d / e as y, x + y as z from t;

-- where a,b,c,d,e are columns of t and x , y, z are computed columns 

-- is valid on almost every database systems, I have just run this on ms sql server 2008.

-- tesu

I genuinely appreciate your response. Because these are computed columns there is no sql statement involved. I have since learned that you cannot compute a column based on other computed columns. :(

For others who may stumble across this thread, here is some basic information on using computed columns at this website: http://www.mssqltips.com/tip.asp?tip=1682 The example given shows using sql for all the work in creating them, but if you have sql server management studio you can just go into the design properties of a table (right click on table), click on the field type and put your formula in the computed columns section.

Hello DAmanding

One live and learn!

I have fiddled about with plenty of computed columns and finally I got this monster solely containing computed columns:

select 4 as "need", 5 as drink, 2 as "of", 6 as "nature", 3 as "now", 1 as "I", 1 as "a", 9 as "alcoholic", cast( 8915./62991.+3 as varchar) as "Wilbur", '<'as '', cast( "now"||'.'||"I"||"need" ||"a"||"drink"||"alcoholic"|| "of"||"nature" as varchar) as "Now I need a drink alcoholic of nature", '<' as ' ', cast(9552./67441.+3 as varchar) as Knorr, cast(WilBur+cast(Knorr as double) -2*"Now I need a drink alcoholic of nature" as varchar) as "Chin-chin!";

which seems to be a true cracker for separating quiche-eater databases from real-programmer databases. Does it also execute on your database showing some telling findings?

-- tesu

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.