|
|
|
SSChasing Mays
 
Group: General Forum Members
Last Login: Thursday, May 17, 2012 10:15 AM
Points: 640,
Visits: 249
|
|
|
|
|
|
SSCommitted
Group: General Forum Members
Last Login: Today @ 1:37 AM
Points: 1,652,
Visits: 1,470
|
|
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]
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 2:46 AM
Points: 2,417,
Visits: 529
|
|
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.
|
|
|
|
|
SSCertifiable
Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 7,661,
Visits: 4,726
|
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: Today @ 5:34 AM
Points: 2,491,
Visits: 2,115
|
|
Good question, and to Carlos - great addition.
Learned something new form both
Steve Jimmo
Sr DBA
It is a narrow mind that cannot see things from more than one point of view. — George Eliot
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: 2 days ago @ 6:00 AM
Points: 1,165,
Visits: 973
|
|
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.
|
|
|
|
|
SSChasing Mays
Group: General Forum Members
Last Login: Tuesday, May 15, 2012 4:33 PM
Points: 619,
Visits: 416
|
|
QOD is a very good one, and the extension by carlos was awesome. Good work guys, got to learn something new today.
Thanks.
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: Today @ 9:48 AM
Points: 2,157,
Visits: 2,077
|
|
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.)
|
|
|
|
|
Right there with Babe
Group: General Forum Members
Last Login: Today @ 10:46 AM
Points: 750,
Visits: 1,019
|
|
Good question. Nice extension - Carlo. Thanks
Regards,
Hardik Doshi
|
|
|
|
|
SSCoach
Group: General Forum Members
Last Login: Today @ 12:34 PM
Points: 16,744,
Visits: 10,102
|
|
|
|
|
