More of computed columns

Question

Post reply

More of computed columns

VM-723206

SSCrazy

Points: 2964
More actions
November 9, 2010 at 8:27 pm

#241070

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

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

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

Carlo Romagnano SSC-Insane Points: 22600 More actions · Answer 1

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 SSCrazy Eights Points: 8440 More actions · Answer 2

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: 259085 More actions · Answer 3

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 More actions · Answer 4

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 More actions · Answer 5

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: 1497 More actions · Answer 6

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 More actions · Answer 7

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 More actions · Answer 8

Hardy21

SSCrazy Eights

Points: 9708

November 11, 2010 at 9:23 pm

#1249806

Good question. Nice extension - Carlo. Thanks

Thanks

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

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