Create Computed Column Using Data from Another Table

SQL Server computed column select from another table

You won't be able to use columns from another table within a computed column expression. This is an extract from the MSDN documentation.

A computed column is computed from an expression that can use other columns in the same table.

You mentioned that your motivation for using a computed column was to increase performance. There are a lot of restrictions but an indexed view might add value here.

Create Computed Column using data from another table

You can create a user-defined function for that:

CREATE FUNCTION dbo.CountUses(@pictureId INT)
RETURNS INT
AS
  BEGIN
      RETURN
        (SELECT Count(id)
         FROM   PictureUse
         WHERE  PictureId = @PictureId)
  END 

The computed column can then be added like this:

ALTER TABLE dbo.Picture
ADD NofUses AS dbo.CountUses(Id)

However, I would rather make a view for this:

CREATE VIEW PictureView
AS
  SELECT Picture.Id,
         PictureName,
         Picture.CreateDate,
         Count(PictureUse.Id) NofUses
  FROM   Picture
         JOIN PictureUse
           ON Picture.Id = PictureUse.PictureId
  GROUP  BY Picture.Id,
            PictureName,
            Picture.CreateDate 

Create calculated column in one table using data from another table in Spotfire?

After a few more hours of tinkering, I figured it out! I thought I would explain what I did here in case anyone else encounters a similar conundrum.

TLDR; Made the desired column in the total table, and added the new column to the monthly table with a left single join on the contract number.

My month and date are tied to a document property which is a drop-down of unique date values in my monthly table. Within the total table, I used a similar code to what I've typed above, but instead of referencing the Date column directly, I referenced the document property, so it was pulling the date from the monthly table into the total table. I also switched from the form IF(condition, SUM()) to SUM(IF()) OVER (). This ultimately summed the correct values. For example, there might be five different types of transactions for each contract, but I only wanted the sum of two types. This resulted in the correct sum being displayed. The sum was displayed in every single cell corresponding to the correct contract number in the new column, so (in the same example) in all 5 contract #1 rows, the sum of the two correct types was displayed.

Then, I went to the data canvas for my monthly table, and added the new column. I chose a left single join, as each contract had only one row in the new table, so that the correct sum would only be displayed once.

End result code:

SUM(IF(Concatenate([Contract_Number],"type-string",[Month_ValDt],[Year_ValDt]) = 
    [Type], [Amount],0)) OVER ([Contract_Number])

Where [Month_ValDt] and [Year_ValDt] are new columns I made in the total table that display the month and ye
ar from the document property that is tied to the date in the monthly table.

Reasoning for the property is that we have a few years of data but I was told to make it dynamic so only one month of data is visible at a time, hence the drop-down.

How can i create Computed Column which gets data from another table

You are looking for JOIN's not computed column.

You can create table valued function or view for this.

Create View taba_tabB 
As
Select A.[Assembly Item],A.Component ,B.Thickness ,B.Notes
From TableA A 
join TableB B on A.Component = B.[Item Id]

formula for computed column based on different table's column

You could create a user-defined function for this:

CREATE FUNCTION dbo.GetValue(@ncode INT, @recid INT)
RETURNS INT
AS 
   SELECT @recid * nvalue 
   FROM c_const 
   WHERE code = @ncode

and then use that to define your computed column:

ALTER TABLE dbo.YourTable
   ADD NewColumnName AS dbo.GetValue(ncodeValue, recIdValue)

define a computed column reference another table

Yes, you can do that - you need a function to count the players for the team, and use that in the computed column:

CREATE FUNCTION dbo.CountPlayers (@TeamID INT)
RETURNS INT 
AS BEGIN
    DECLARE @PlayerCount INT

    SELECT @PlayerCount = COUNT(*) FROM dbo.Player WHERE TeamID = @TeamID

    RETURN @PlayerCount
END

and then define your computed column:

ALTER TABLE dbo.Team
ADD TotalPlayers AS dbo.CountPlayers(ID) 

Now if you select, that function is being called every time, for each team being selected. The value is not persisted in the Team table - it's calculated on the fly each time you select from the Team table.

Since it's value isn't persisted, the question really is: does it need to be a computed column on the table, or could you just use the stored function to compute the number of players, if needed?

Is it possible to have a generated column based on another table?

You can't do that with a computed column. If you wanted to store and maintain such information, you would need trigger code for every DML operation on table asset , which makes things rather complex.

You can, on the other hand, create a view:

create view v_equipment
select e.*, 
    (select count(*) from asset a where a.equipment_id = e.equipment_id) as quantity
from equipment e

This gives you an always up-to-date perspective at your data. You can query the view directly instead of the table whenever you need the quantity information.

---

Related Topics