November 9, 2009 at 10:12 pm
Hi,
Can we used the Computed Columns to create another computed Column?
Like
Column A = B+C
Column E = A+D
We can use this like E = (B+C)+D?
But we do we have any other option for this?
Thanks
Deeps
November 16, 2009 at 10:55 pm
Hm.. Never tried that.. I bet it wouldn't be difficult to test..
CEWII
November 17, 2009 at 1:56 am
No, you can't use a computed column as source for another computed column
The main reason I think is circular reference, like this:
A = B
B = A
N 56°04'39.16"
E 12°55'05.25"
November 17, 2009 at 8:09 am
I figured you couldn't but it was simple enough to test yourself..
CEWII
May 12, 2016 at 2:18 am
This is a pain as it make the compute code massive in my case so I made them UDF like below
A
B
C = A+B
D = SumABX(A,B)
Function SumABX(A, B)
begin
return (a+b)*2
end
This may help
May 12, 2016 at 5:57 am
Matthew Saggers-700210 (5/12/2016)
This is a pain as it make the compute code massive in my case so I made them UDF like belowA
B
C = A+B
D = SumABX(A,B)
Function SumABX(A, B)
begin
return (a+b)*2
end
This may help
why could you not just put the same calculation in as the calculated value? then you have a properly script-able table, with no dependencies to other functions
the example of the function is probably not a trivial calculation like you demonstrated , but there's i'd prefer to be able to script the table for portability;
CREATE TABLE #Example2(
A int,
B int,
C AS A+B PERSISTED,
D AS (A+B)*2 PERSISTED)
Lowell
May 12, 2016 at 6:12 am
Lowell, yes this is the way to go. I found when the compute is quite complex like
A int
B int
c int
D as A+B
E as ((A+B) / c) / A+B
F as D - (A+B)
In my case I had 10 fields that where normal data and 20 computed fields with a mixture of the 10 and some of those computes like E where then used in other computes so having a function like SaleExTax(Sales, TaxRate) so it easier to read, also as it a value function I could use it in other stored procedures give us code reuse.
May 12, 2016 at 6:54 am
Just be aware that scalar udfs would prevent parallelism on all the queries they're used. A complex calculation won't have this problem.
May 12, 2016 at 9:52 am
Matthew Saggers-700210 (5/12/2016)
Lowell, yes this is the way to go. I found when the compute is quite complex likeA int
B int
c int
D as A+B
E as ((A+B) / c) / A+B
F as D - (A+B)
In my case I had 10 fields that where normal data and 20 computed fields with a mixture of the 10 and some of those computes like E where then used in other computes so having a function like SaleExTax(Sales, TaxRate) so it easier to read, also as it a value function I could use it in other stored procedures give us code reuse.
You could also use a view, with CROSS APPLYs to supply the alias names. CAs can refer to earlier CA/alias names. Then use the view instead of the table name in all queries:
CREATE VIEW <view_name>
AS
SELECT tn.*, ca1.*, ca2.*
FROM table_name tn
CROSS APPLY (
SELECT D = tn.A+tn.B
) AS ca1
CROSS APPLY (
SELECT E = D / tn.c / tn.A+tn.B,
F = D - tn.G
) AS ca2
Edit: Put CREATE VIEW code into a SQL code block.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply