Computed Column Specification

Hello,
I want to assign a column a computed value, which is the multiplication
of a value from the table within and a value from another table.
How can I do that?
Say the current table is A, column1; and the other table is B, column3.
What should I write as formula?
I tried someting like;
column1 * (SELECT column3 FROM B WHERE A.ID = B.ID)
but it didn't work.On 10 Sep 2006 13:49:17 -0700, Dot Net Daddy wrote:
>Hello,
Hi Dot Net Daddy,
I have just replied to the same question in comp.databases.ms-sqlserver.
In the future, please post your questions to one newsgroup only. That
makes it easier for us to help you, since we can easily see what answers
you've already had, without having to search in other groups as well.
Only questions that really falls between two stools should be posted to
multiple groups - and even then, they should be crossposted (i.e. one
copy that appears in all groups, with answers also appearing in all
groups) rather than multiposted (seperate copies of the message).
--
Hugo Kornelis, SQL Server MVP

Computed Column Specification

Hello,

I want to assign a column a computed value, which is the multiplication

of a value from the table within and a value from another table.

How can I do that?

Say the current table is A, column1; and the other table is B, column3.

What should I write as formula?

I tried someting like;

column1 * (SELECT column3 FROM B WHERE A.ID = B.ID)

but it didn't work.On 10 Sep 2006 14:41:16 -0700, Dot Net Daddy wrote:

Quote:

Originally Posted by

>Hello,
>
>I want to assign a column a computed value, which is the multiplication
>
>of a value from the table within and a value from another table.

(snip)

Hi Dot Net Daddy,

Here's what Books Online has to say:

Quote:

Originally Posted by

>computed_column_expression
>Is an expression that defines the value of a computed column. (...) The
>expression can be a noncomputed column name, constant, function,
>variable, and any combination of these connected by one or more
>operators. The expression cannot be a subquery or contain alias data
>types.

So you can't embed a subquery in a computed column expression, but
user-defined functions are okay.

CREATE dbo.MyUDF (@.A_ID AS int)
RETURNS int
AS
RETURN (SELECT column3 FROM B WHERE B.ID = @.A_ID)
go

And then defined the computed column as

ALTER TABLE MyTable
ADD NewColumn AS column1 * dbo.MyUDF(ID)
go

--
Hugo Kornelis, SQL Server MVP|||Hugo Kornelis (hugo@.perFact.REMOVETHIS.info.INVALID) writes:

Quote:

Originally Posted by

So you can't embed a subquery in a computed column expression, but
user-defined functions are okay.
>
CREATE dbo.MyUDF (@.A_ID AS int)
RETURNS int
AS
RETURN (SELECT column3 FROM B WHERE B.ID = @.A_ID)
go
>
And then defined the computed column as
>
ALTER TABLE MyTable
ADD NewColumn AS column1 * dbo.MyUDF(ID)

However, this is not a very good idea. The performance cost can be horrible.

I once made an experiment where I added a CHECK constraint which included
a UDF, and that replaced a trigger test. Inserting 25000 rows into the table
took 1-2 seconds without the UDF, and 30 seconds with.

So in the end, defining a view is probably the way to do.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Computed Column Specification

Hello,
I want to assign a column a computed value, which is the multiplication
of a value from the table within and a value from another table.
How can I do that?
Say the current table is A, column1; and the other table is B, column3.
What should I write as formula?
I tried someting like;
column1 * (SELECT column3 FROM B WHERE A.ID = B.ID)
but it didn't work.On 10 Sep 2006 13:49:17 -0700, Dot Net Daddy wrote:

>Hello,
Hi Dot Net Daddy,
I have just replied to the same question in comp.databases.ms-sqlserver.
In the future, please post your questions to one newsgroup only. That
makes it easier for us to help you, since we can easily see what answers
you've already had, without having to search in other groups as well.
Only questions that really falls between two stools should be posted to
multiple groups - and even then, they should be crossposted (i.e. one
copy that appears in all groups, with answers also appearing in all
groups) rather than multiposted (seperate copies of the message).
Hugo Kornelis, SQL Server MVPsqlsql