Calculating Fields

[fiber0pti]

I'm trying to set a field equal to the math of other fields. Example.
I want the field NetProfit to equal TotalGrossProfit-TotalOverHeadAdj, which are both fields also. How would I do this? Does it have something to do with the default value?

 

[tlbroadbent]

You can calculate in a query Select statement and name the resulting column using an alias.

SELECT TotalGrossProfit, TotalOverHeadAdj, TotalGrossProfit-TotalOverHeadAdj As NetProfit
FROM table1 Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost

 

[tlbroadbent]

You can calculate in a query Select statement and name the resulting column using an alias.

SELECT TotalGrossProfit, TotalOverHeadAdj, TotalGrossProfit-TotalOverHeadAdj As NetProfit
FROM table1 Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost

 

[fiber0pti]

I'm not sure I understand. I want to calculate the default value for a field. Is that how I would do it?

 

[tlbroadbent]

Do you want to store the value in the table? It is generally not a good idea to store the results of a calculation in another column. In a normalized relational database, couln values cannot depend on values of other columns.

Only a constant value, such as a character string; a system function, such as SYSTEM_USER(); or NULL can be used as a default. If you want to store the result of the calculation in the table, you can create a TRIGGER to update the column whenver the record is inserted or updated. Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost

 

[Guest_imported]

No. You can not store the calculations of two columns as the default of a third column. My suggestion would be to use a Trigger to calculate the third column based on the values of the two previous columns. The use of triggers will allow you to keep the columns accurate during an insert, update and delete.

 

[redlam]

Good advice guys! BUT... if you are determined to create a computed column in a table, I believe SQL7 and SQL2000 will allow you to do so without a trigger. Here's a sample script that works in SQL2000:

create table table1
(myid int,
value1 int,
value2 int,
comp_col as value1 + value2)

The thing to understand about computed columns is that the value is generated on the fly when accessed. This is different from an insert trigger which calculates and stores the value when data is inserted and/or updated.

 

[redlam]

I may have misunderstood but even though a computed column would not be a 'default value', it would calculate a value for every record that has qualifying data.