Menu
Consider this table: c_const
code | nvalue -------------- 1 | 10000 2 | 20000 and another table t_anytable
rec_id | s_id | n_code --------------------- 2 | x | 1 The goal is to have s_id be a computed column, based on this formula:
rec_id*(select nvalue from c_const where code=ncode) This produces an error:
Subqueries are not allowed in this context. Only scalar expressions are allowed.
How can I calculate the value for this computed column using another table's column as an input?
906
While you can't reference another table's column directly within your expression, you can invoke a user-defined function. And therefore, you could create a user-defined function that performs the calculation you need, then simply call that function as your computed column's expression.
Go to your database, right click on tables, select “New Table” option. Create all columns that you require and to mark any column as computed, select that column and go to column Properties window and write your formula for computed column.
Get a list of computed columns in a SQL Server database. We can use the system function sys. computed_columns and join it with the sys. objects to get a list of available computed columns, their data type, and the column definition (formula).
Use SQL Server Management StudioRight-click Columns and select New Column. Enter the column name and accept the default data type (nchar(10)). The Database Engine determines the data type of the computed column by applying the rules of data type precedence to the expressions specified in the formula.
You could create a user-defined function for this:
CREATE FUNCTION dbo.GetValue(@ncode INT, @recid INT) RETURNS INT AS SELECT @recid * nvalue FROM c_const WHERE code = @ncode and then use that to define your computed column:
ALTER TABLE dbo.YourTable ADD NewColumnName AS dbo.GetValue(ncodeValue, recIdValue) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
