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 9, 2010 8:27 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, July 23, 2013 6:34 AM
Points: 654, Visits: 265
Comments posted to this topic are about the item More of computed columns
Post #1018353
Posted Wednesday, November 10, 2010 1:06 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:53 AM
Points: 2,529, Visits: 2,402
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


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 1:54 AM
Points: 3,036, Visits: 928
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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:54 PM
Points: 13,735, Visits: 10,705
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: Monday, July 14, 2014 9:47 AM
Points: 2,919, Visits: 2,525
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
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 11:26 AM
Points: 668, Visits: 485
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: Friday, September 5, 2014 2:00 PM
Points: 2,163, Visits: 2,191
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


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, September 7, 2014 10:22 PM
Points: 1,126, Visits: 1,387
Good question. Nice extension - Carlo. Thanks

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


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:32 PM
Points: 21,755, Visits: 15,454
Thanks for the question.



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


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