Can you do a calculated test field with data from two tables?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Can you do a calculated test field with data...

Can you do a calculated test field with data from two tables?

Rate Topic

Display Mode

Topic Options

Author

Message

kstone-869270

Posted Friday, May 21, 2010 1:11 PM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, June 27, 2010 12:01 PM
Points: 5, Visits: 14

If you had a single table like this:

CREATE TABLE PERSON (
[PersonId] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](50) NOT NULL,
[LastName] [nvarchar](50) NOT NULL,
[FullName] AS (([FirstName]+' ')+[LastName])

a calculated field is easy. What I really want is something like this:

CREATE TABLE SURNAME (
[SurNameId] [int] IDENTITY(1,1) NOT NULL,
[SurName] [nvarchar](50) NOT NULL)

CREATE TABLE PERSON (
[PersonId] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](50) NOT NULL,
[SurNameId] int NOT NULL,
[FullName] AS (([FirstName]+' ')+SURNAME.[SurName])

obviously this specific example is somewhat overzealous normalization, but this was the easiest way to express the problem. What's I'm attempting to do is make the database more friendly to O/R mappers and a view will cause them to not generate update code. I could create a trigger to update the field and persist the data but I was attempting to avoid that.

Is something like this possible in SQL server?

Post #926238

Eugene Elutin

Posted Friday, May 21, 2010 1:20 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 11:22 AM
Points: 2,541, Visits: 4,370

You can create view for that

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!" BigGrin

(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help

Post #926242

kstone-869270

Posted Friday, May 21, 2010 1:41 PM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, June 27, 2010 12:01 PM
Points: 5, Visits: 14

Yes I know, but as I noted above if I do that O/R mappers will not generate update code because views aren't updatable (in SQL server). I could also simple change the results of the O/R mapper after the generation to use a view for the lookup, but that means that change must be repeated each time the code is generated. Not a big deal if you're nearing the end of development, a much bigger deal early on.

The actual data design is more complex than the example above, it's merely a simple way to get to the root of the problem. The desire is to define display constructs in the SQL code that both encourage normalization and maintain ease of use with development tools.

Post #926251

Eugene Elutin

Posted Friday, May 21, 2010 1:47 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 11:22 AM
Points: 2,541, Visits: 4,370

you can update view in SQL Server. Read about "instead of" triggers
BTW, what you're trying to do is looking very wrong...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!" BigGrin

(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help

Post #926255

Eugene Elutin

Posted Friday, May 21, 2010 1:49 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 11:22 AM
Points: 2,541, Visits: 4,370

Also, you can create trigger on insert/update which will look up your surname...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!" BigGrin

(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help

Post #926257

Garadin

Posted Friday, May 21, 2010 2:12 PM

SSCommitted

Group: General Forum Members
Last Login: Wednesday, November 07, 2012 4:08 PM
Points: 1,525, Visits: 4,047

As elutin points out, you have options, but if you definitively want a calculated field that accesses data from another table, you have to create a function and then call it with the calculated field. The function can access multiple tables, but is schema bound once you add it to the table definition (You can't change the function after the fact unless you remove it from the table definition) and the values cannot be persisted. (Meaning it will run once per row every time you select it out of the table). Depending on usage, this is obviously not ideal.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. Wink

Links: How to Post Sample Data :: Running Totals :: Tally Table :: Cross Tabs/Pivots :: String Concatenation

Post #926265

kstone-869270

Posted Friday, May 21, 2010 2:13 PM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, June 27, 2010 12:01 PM
Points: 5, Visits: 14

Yes, I know I can create a trigger, yes I know I can create a view. A trigger means the data is persisted, the view tells the O/R mappers not to generate update code. Yes, I know I can do this by hand. What I am looking for is an alternative that won't interfere with automated code generation. I have dozens and dozens of tables, not just two as in the example, and this is a recurring issue. We want to keep the data normalized to maintain integrity, but still allow round-tripping code with O/R mappers. Persisting the data with a trigger means an order of magnitude increase in storage, obtainable but undesirable.

If a calculated field with two tables isn't possible, fine, but that's the question, not how can I do it other ways.

Post #926266

kstone-869270

Posted Friday, May 21, 2010 2:33 PM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, June 27, 2010 12:01 PM
Points: 5, Visits: 14

Thank you.

So something like:

FullName As (Firstname + ' ' + dbo.SurnameLookup(SurnameId))

is what you're suggesting? Sounds doable. Long term the performance impact can be measured and troublesome lookups and be manually replaced with views after the fact. We specifically DON'T want the data persisted to that's fine.

Post #926277

Garadin

Posted Friday, May 21, 2010 3:19 PM

SSCommitted

Group: General Forum Members
Last Login: Wednesday, November 07, 2012 4:08 PM
Points: 1,525, Visits: 4,047

Yep, that's the gist of it.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. Wink

Links: How to Post Sample Data :: Running Totals :: Tally Table :: Cross Tabs/Pivots :: String Concatenation

Post #926299

kstone-869270

Posted Friday, May 21, 2010 3:19 PM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, June 27, 2010 12:01 PM
Points: 5, Visits: 14

For sake of completeness the solution was:

CREATE TABLE SURNAME (
[SurNameId] [int] IDENTITY(1,1) NOT NULL,
[SurName] [nvarchar](50) NOT NULL)

create function [dbo].[SurnameLookup](@SurNameId int)
returns nvarchar(50)
as
BEGIN
Return (Select SurName from SURNAME where SurNameId = @SurNameId)
END

CREATE TABLE PERSON (
[PersonId] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](50) NOT NULL,
[SurNameId] int NOT NULL,
[FullName] AS (([FirstName]+' ')+dbo.SurnameLookup(SurNameid))

Thanks for the information.

Post #926300

« Prev Topic | Next Topic »

Permissions