Menu
I have a select statement with calculated columns and I would like to use the value of one calculated column in another. Is this possible? Here is a contrived example to show what I am trying to do.
SELECT [calcval1] = CASE Statement, [calcval2] = [calcval1] * .25
274
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).
You can only reference a calculated column in the order by clause. For any other use either use a sub-query or repeat the logic.
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.
No.
All the results of a single row from a select are atomic. That is, you can view them all as if they occur in parallel and cannot depend on each other.
If you're referring to computed columns, then you need to update the formula's input for the result to change during a select.
Think of computed columns as macros or mini-views which inject a little calculation whenever you call them.
For example, these columns will be identical, always:
-- assume that 'Calc' is a computed column equal to Salaray*.25
SELECT Calc, Salary*.25 Calc2 FROM YourTable
Also keep in mind that the persisted option doesn't change any of this. It keeps the value around which is nice for indexing, but the atomicity doesn't change.
50
Unfortunately not really, but a workaround that is sometimes worth it is
SELECT [calcval1], [calcval1] * .25 AS [calcval2]
FROM (SELECT [calcval1] = CASE Statement FROM whatever WHERE whatever)
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
