Using computed Columns to Create another Computed Column

  • 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

  • Hm.. Never tried that.. I bet it wouldn't be difficult to test..

    CEWII

  • 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"

  • I figured you couldn't but it was simple enough to test yourself..

    CEWII

  • 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

  • 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 below

    A

    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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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.

  • Just be aware that scalar udfs would prevent parallelism on all the queries they're used. A complex calculation won't have this problem.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Matthew Saggers-700210 (5/12/2016)


    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.

    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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply