Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 793
  • Last Modified:

How to include a foreign value in a computed column?

How does one include a foreign value in a computed column within SQL Server?  The goal is to display a particular field, from the parent table, in the child table.

Here are a few example tables to help explain:

     TABLE: Client
     COLUMNS:
     ClientID int PK
     ClientName char(30)

     TABLE: ClientLocation
     COLUMNS:
     LocationID int PK
     ClientID int FK
     LocationName char(30)
     DisplayName COMPUTED

If I have two clients:

     Cool Company
     Outstanding Client

And, if each client has two locations:

     Main Office
     Warehouse

The goal is for the "DisplayName" column on ClientLocation to show the following:

     Cool Company (Main Office)
     Cool Company (Warehouse)
     Outstanding Client (Main Office)
     Outstanding Client (Warehouse)

From what I can tell, it is not possible to do a join within the formula of a computed column.  I can come close by creating custom functions for every possible column on every possible table that I want to get back, however that is not very practical.

Thanks, in advance, for taking the time to respond.
     
 
0
SkydiverFL
Asked:
SkydiverFL
  • 9
  • 8
  • 2
1 Solution
 
Daniel WilsonCommented:
A computed field won't do that.  You'll need a trigger.

Looking at the example ... Display Name would be better in a view.  But if you really want it in the Client Location table ...

You could
  1. put an Update trigger on Client that will check if ClientName has been changed and, if so, update all the ClientLocation records that are tied to it.
  2. put an Insert trigger on ClientLocation to set the DisplayName
  3. put an Update trigger on ClientLocation to set the DisplayName if the LocationName is modified
Need some code?
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
You cannot use a subquery while creating a computed column.

Option 1:
   But you can achieve it in the other way around like given below:

ALTER TABLE ClientLocation ADD COLUMN CLIENTNAME VARCHAR(30)
ALTER TABLE ClientLocation ADD COLUMN LOCATIONCOM rtrim(CLIENTNAME) + rtrim(LocationName )

Now populate both the values in this table and then create a computed column.

Option 2: ( My choice)
   Create a view with the computed column:

create view viewname as
select  t1.LocationID, t1.ClientID, t1.LocationName, rtrim(t2.ClientName ) + rtrim(t1.LocationName) as DisplayName
from ClientLocation  t1, Client t2
where t1.ClientID = t2.ClientID
0
 
SkydiverFLAuthor Commented:
Thanks for replying, guys.  One small piece of info that may help...

This is being used through a code generator.  Unfortunately, this means I cannot [easily] modify the outbound SQL or use triggers.  I need to find some way that I can "template-ize".  This is why I [unfortunately] keep going to back to the functions... which I would rather not do.

The function should give me the ability to do something like...

   getClient_ClientName(ClientID) + ' (' + LocationName + ')'

It's ugly and, if I went this route, I would end up with one function for every column in the database... ugly AND messy.  But, the DisplayName column needs to remain as simple as possible.

I hope this helps.  Thanks, again.
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
SkydiverFLAuthor Commented:
No ideas?  I would really hate to build one function for every column in the database.
0
 
Daniel WilsonCommented:
If you can't use a trigger or a view ... I'm afraid you can't do it.  Sorry!
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
<< This is being used through a code generator.  Unfortunately, this means I cannot [easily] modify the outbound SQL or use triggers. >>

Why cant you create a view as I mentioned earlier.
0
 
SkydiverFLAuthor Commented:
Because a view does not offer the amount of flexibility needed for the generator.  The goal is to be able to have a computed field and build the output value however the developer needs.  

In short, the process would be...

A. Run some magic process to create the functions, or whatever resources are needed for the existing tables;

B. Add the computed fields wherever needed throughout the database; and,

C. Run the generator to output the needed objects (class files, etc.).

So, even though a view could be created to build unique values, it would have no impact on the original table.  Meaning, the generator would not touch the view when examining the table in question.

I hope that helps answer the question.  The bottom line is that I need to figure out a way for a computed column to display data from a foreign table.  That is still the question on the table.
0
 
Daniel WilsonCommented:
>>The bottom line is that I need to figure out a way for a computed column to display data from a foreign table.

That is, by design, not possible in SQL Server 2005.

Would an updateable view work for you?  So your app thinks there's just the view ... but in reality there is a table (or multiple tables) behind it?  Can your code generator be used to create the backend table(s), the view, and the triggers on the view?

Or can you extend your code generator?  If you can get it to add a trigger, then we can get you the rest of the way.
0
 
Daniel WilsonCommented:
The same restriction holds in SQL 2008:  http://msdn.microsoft.com/en-us/library/ms174979.aspx
          computed_column_expression                    
Is an expression that defines the value of a computed column. A computed column is a virtual column that is not physically stored in the table, unless the column is marked PERSISTED. The column is computed from an expression that uses other columns in the same table. For example, a computed column can have the definition: cost AS price * qty. 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.
0
 
SkydiverFLAuthor Commented:
Thanks for the reply.  =)

The generator can be extended SLIGHTLY.  For example, it can build or alter tables, triggers, costraints, and the like, in a "pre-generation" step.  Unfortunately, this can / will significantly bloat the database... which is what I am trying to prevent.

For example, if I use functions to accomplish grabbing the foreign values, I need to have the generator run a "pre" step to build a function for every possible column in the database.  This is the only way to know that a certain field can be obtained.  Unfortunately, if there are 100 tables, each with 10 non-key columns, that means I'll have 1000 functions added to the database.  And, in all reality, only a handful of them will ever be used.

I hope this info helps.  Thanks, again, for lending your brainpower.
0
 
SkydiverFLAuthor Commented:
What about the function idea?  Any idea how to make the function super generic so I can pass in more arguments?

For example, rather than doing this:

     getClient_ClientName(ClientID) + ' (' + LocationName + ')'

To do something like this:

     getClient(ClientID, 'ClientName')

In theory, this would allow me to pass in the CURRENT ID of the parent (the ClientID value from the current row in the ClientLocation table) and then pass in the NAME of the desired column to return.  This would allow there to be one function per table in the database.

An even better option would be this:

     getValue('Client', 'ClientID', ClientID, 'ClientName')

This example would tell a very COMMON function to look at the 'Client' table (the first value) and use the 'ClientID' column (the second value) to find the value passed in by the third value, "ClientID".  And, when found, return the value in the "ClientName" column.

Any idea how to do this?  This would seem to be a medium of the current situation with the proposed solution.
0
 
Daniel WilsonCommented:
Any of those will require dynamic SQL.  I have used dynamic SQL only in Stored Procedures, not Functions, but AFAIK, it can be used in Functions.

Now ... before you go too far, you want to make sure that use of such a function -- that looks at rows in another table -- is actually legal in a computed column.
0
 
SkydiverFLAuthor Commented:
Uh huh... can you get it to work?  I cannot.
0
 
Daniel WilsonCommented:
Can I see the code you're working w/?  And the error / failure?
0
 
SkydiverFLAuthor Commented:
Nope.  Sorry.  I could never get the SQL to execute without errors and, so, never saved it.  Nothing with the function was very complex, however.
0
 
Daniel WilsonCommented:
The following is close ... but won't work b/c @RetVal is out of the scope of the EXEC statement.

Is this something like you were trying?

Create Function dbo.GetValue(@TableName varchar(20), @KeyName varchar(20), @KeyVal int, @FieldName varchar(20))
  Returns varchar(100)
AS
Begin
 Declare @SQL varchar(max)
 Declare @RetVal varchar(100)
 Set @SQL = 'Select @RetVal = ' + @FieldName + ' From ' + @Table + ' Where ' + @KeyName + ' = ' + cast(@KeyVal as varchar(20))
 exec (@SQL)
 
 Return @RetVal
End

Open in new window

0
 
SkydiverFLAuthor Commented:
That's pretty much what I had... basically dynamically built SQL in a function.  I could not get it to work because of various errors.  In fact, here's the error from yours:

Msg 443, Level 16, State 14, Procedure GetValue, Line 8
Invalid use of side-effecting or time-dependent operator in 'EXECUTE STRING' within a function.
0
 
Daniel WilsonCommented:
I see.  Yeah, I'm getting that too.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60857
"you can't use dynmaic sql in a function. period."

"the only workaround is in 2005
you can call a CLR function inside the UDF function, if you trick SQL server into thinking that that CLR has not side effects and is deterministic. But SQL server can not verify it, so, you can do all sorts of dirty things inside your .Net code. :)"

I don't think we even want to think about that workaround.  Sounds pretty vicious.


0
 
SkydiverFLAuthor Commented:
Just a quick recap before I award points...

I was looking for a way to use values from a foreign table in a computed column.  DanielWilson and rrjegan17 replied saying it could not be done.  I then replied with a solution that DOES work (using a function).  Since then, there has not been any reply with a solution that did work.  Daniel, however, did stick it out until we were all exhausted.

Sorry for the reply.  I'm just hopeful that there is a working solution OTHER than the one I offered as an example.  Again, I ultimately want a BETTER way to do it.
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 9
  • 8
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now