Does it possible to bind different table column data in original table column in computed column specification properties?

Question

Does it possible to bind different table column data in original table column in computed column specification properties?

Virtual Reality

Ten Centuries

Points: 1182
More actions
March 1, 2016 at 10:19 am

#308440

Hello,
I want to add different table column in original table's computed column .In Biograph_Master_UDF's imagename column I want to add Name_Master.Last_Name+Name_Master.First_Name+BIOGRAPH_Master_UDF.ID_NUM. But When I was trying to implement this code in imagename's computed column specification properties I get an error that
(1)'BIOGRAPH_MASTER_UDF' table
- Error validating the formula for column 'imagename'.
(2)'BIOGRAPH_MASTER' table saved successfully
'BIOGRAPH_MASTER_UDF' table
- Unable to modify table.
The multi-part identifier "Name_Master.Last_Name" could not be bound.
The multi-part identifier "Name_Master.First_Name" could not be bound.
How could I solve this error message?
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

djj SSCoach Points: 18831 More actions · Answer 1

ripas (3/1/2016)
Hello,
I want to add different table column in original table's computed column .In Biograph_Master_UDF's imagename column I want to add Name_Master.Last_Name+Name_Master.First_Name+BIOGRAPH_Master_UDF.ID_NUM. But When I was trying to implement this code in imagename's computed column specification properties I get an error that
(1)'BIOGRAPH_MASTER_UDF' table
- Error validating the formula for column 'imagename'.
(2)'BIOGRAPH_MASTER' table saved successfully
'BIOGRAPH_MASTER_UDF' table
- Unable to modify table.
The multi-part identifier "Name_Master.Last_Name" could not be bound.
The multi-part identifier "Name_Master.First_Name" could not be bound.
How could I solve this error message?
Thanks

If I understand (code would be nice) I would guess BIOGRAPH_Master_UDF.ID_NUM should be cast as character at the least. Without the code I personally cannot help, someone else might be able.

Lowell SSC Guru Points: 323518 More actions · Answer 2

this almost sounds like ID_NUM is a foreign key, and you want to render the details inline in the table; why not just create a view that pulls the data together? why try and use a function in a computed column?

i guess you could do it, here's an example i put together, but not sure why you would wnat to do that.

create table City(CityID int primary key,CityName varchar(30) )

INSERT INTO City

SELECT 1,'Springfield' UNION ALL

SELECT 2,'Clinton' UNION ALL

SELECT 3,'Madison' UNION ALL

SELECT 4, 'Franklin'

CREATE TABLE Addresses (Address1 varchar(30),CityID int)

INSERT INTO Addresses SELECT '123 street',1

GO

CREATE FUNCTION GetCity(@cityId int)

returns varchar(30)

AS

BEGIN

DECLARE @val varchar(30)

SELECT @val = CityName FROM City WHERE CityId = @cityId

RETURN @val

END

GO

SELECT dbo.GetCity(1) --Springfield

GO

ALTER TABLE Addresses ADD CityName AS dbo.GetCity(CityID)

SELECT * FROM Addresses

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

drew.allen SSC Guru Points: 76996 More actions · Answer 3

Based on the UDF in your Biograph_Master_UDF, I'm assuming that this is a user-defined function that returns a table. Computed columns can only call user-defined functions that return a scalar value. You'll probably also need to schema qualify this UDF to get it to work correctly.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Sergiy SSC Guru Points: 110208 More actions · Answer 4

djj (3/1/2016)
ripas (3/1/2016)
Hello,
I want to add different table column in original table's computed column .In Biograph_Master_UDF's imagename column I want to add Name_Master.Last_Name+Name_Master.First_Name+BIOGRAPH_Master_UDF.ID_NUM. But When I was trying to implement this code in imagename's computed column specification properties I get an error that
(1)'BIOGRAPH_MASTER_UDF' table
- Error validating the formula for column 'imagename'.
(2)'BIOGRAPH_MASTER' table saved successfully
'BIOGRAPH_MASTER_UDF' table
- Unable to modify table.
The multi-part identifier "Name_Master.Last_Name" could not be bound.
The multi-part identifier "Name_Master.First_Name" could not be bound.
How could I solve this error message?
Thanks
If I understand (code would be nice) I would guess BIOGRAPH_Master_UDF.ID_NUM should be cast as character at the least. Without the code I personally cannot help, someone else might be able.

You're using values from another table - Name_Master.Last_Name+Name_Master.First_Name - without specifying the selection criteria.

SQL server cannot figure out which record from table Name_Master its supposed to use for each particular record in BIOGRAPH_MASTER_UDF, therefore it sends you that error message.

_____________
Code for TallyGenerator