More of computed columns

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

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

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

  • 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

  • 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

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

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

    Thanks.

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

  • Good question. Nice extension - Carlo. Thanks

    Thanks

  • 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