More of computed columns

  • VM-723206

    SSCrazy

    Points: 2964

    Comments posted to this topic are about the item More of computed columns

  • Carlo Romagnano

    SSC-Insane

    Points: 21948

    To get values from other tables or columns of the same table but different row, you should use a function:

    CREATE TABLE [dbo].[a2]

    (

    [a1ID] [int] NULL,

    [col3] AS (a1ID^2)

    )

    GO

    create function get_a2_col3(@a1id int)

    returns int

    as

    begin

    return (select col3 from [dbo].[a2] where a1ID = @a1id)

    end

    go

    CREATE TABLE [dbo].[a3]

    (

    [a3ID] [int] NULL,

    [col4] AS dbo.get_a2_col3(a3ID) --(dbo.a2.col3 - 1)

    )

    GO

    drop table [a2]

    drop table [a3]

  • Rune Bivrin

    SSCertifiable

    Points: 7794

    Good question!

    I was pretty sure that the first create would fail since the operator ^ isn't described in BOL under arithmetic operators. It never occured to me that it could be a bitwise operator. I just assumed it was an attempt to make me think it was a "to the power of" operator.

    DOH!


    Just because you're right doesn't mean everybody else is wrong.

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Nice question.

    And thanks to Carlo for the add-on.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • sjimmo

    SSChampion

    Points: 11139

    Good question, and to Carlos - great addition.

    Learned something new form both

    Steve Jimmo
    Sr DBA
    “If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

  • SanDroid

    SSChampion

    Points: 10068

    I gift this question fife stars for knot havings type o's and shows correct referance meaterials and knowledge of SQL feartures.

    I give the extra star to Carl for the post of how a function can include the column data from another table in a computed column.

    :smooooth:

  • Surii

    Default port

    Points: 1477

    QOD is a very good one, and the extension by carlos was awesome. Good work guys, got to learn something new today.

    Thanks.

  • UMG Developer

    SSChampion

    Points: 13482

    Thanks for the question.

    One small point, not only did the create for the second table fail, so did the drop. (Though I doubt that would trip anybody up.)

  • Hardy21

    SSCrazy Eights

    Points: 9708

    Good question. Nice extension - Carlo. Thanks

    Thanks

  • SQLRNNR

    SSC Guru

    Points: 281243

    Thanks for the question.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 10 posts - 1 through 10 (of 10 total)

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