Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

More of computed columns Expand / Collapse
Author
Message
Posted Tuesday, November 09, 2010 8:27 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Saturday, April 06, 2013 12:20 AM
Points: 649, Visits: 263
Comments posted to this topic are about the item More of computed columns
Post #1018353
Posted Wednesday, November 10, 2010 1:06 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 1,996, Visits: 1,864
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]

Post #1018400
Posted Wednesday, November 10, 2010 1:06 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 4:35 AM
Points: 2,673, Visits: 729
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.
Post #1018401
Posted Wednesday, November 10, 2010 3:23 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 6:30 AM
Points: 9,410, Visits: 6,495
Nice question.
And thanks to Carlo for the add-on.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1018456
Posted Wednesday, November 10, 2010 6:08 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, June 14, 2013 7:34 AM
Points: 2,865, Visits: 2,472
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
Post #1018517
Posted Wednesday, November 10, 2010 7:29 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,232, Visits: 1,046

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.
Post #1018603
Posted Wednesday, November 10, 2010 7:59 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, June 13, 2013 10:57 AM
Points: 659, Visits: 465
QOD is a very good one, and the extension by carlos was awesome. Good work guys, got to learn something new today.

Thanks.
Post #1018634
Posted Wednesday, November 10, 2010 9:53 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:45 AM
Points: 2,163, Visits: 2,151
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.)
Post #1018766
Posted Thursday, November 11, 2010 9:23 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 7:11 AM
Points: 877, Visits: 1,159
Good question. Nice extension - Carlo. Thanks

Thanks
Post #1019687
Posted Wednesday, November 24, 2010 11:44 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 10:31 AM
Points: 18,857, Visits: 12,442
Thanks for the question.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1026099
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse