January 28, 2013 at 10:46 am
How to create a computed column based on the Title nvarchar(max) column of another field: I want to make Title in table 1 a computed column from table2 based on docId=DocId. I keep trying to add it as computed column in SSMS and it wont let me.
Table1
AssetId Title DocId
1 10
========================
Table2
DocId Title
=========================
5 Disaster
10 Recovery
15 Pending
CREATE FUNCTION dbo.GetValue (@DocId INT)
RETURNS TABLE
AS
RETURN
(SELECT T1.AssetId, T1.DocId, T2.Title
FROM Table1 T1
JOIN Table2 T2
ON T1.DocId = T2.DocId
WHERE T1.DocID = @DocId
)
GO
--I was messing with this code, but its not working, I want to be able to make Table1 Title a computed column based on Table 2 Title column and documentId
ALTER TABLE dbo.Table1
ADD Title AS dbo.GetValue(Title)
I get errors.
January 28, 2013 at 10:56 am
The way you have your GetValue function built, you need to put the DocID in as your arguement in the function, not the title.
January 28, 2013 at 11:06 am
mail4bbt (1/28/2013)
How to create a computed column based on the Title nvarchar(max) column of another field: I want to make Title in table 1 a computed column from table2 based on docId=DocId. I keep trying to add it as computed column in SSMS and it wont let me.
Table1AssetId Title DocId
1 10
========================
Table2
DocId Title
=========================
5 Disaster
10 Recovery
15 Pending
CREATE FUNCTION dbo.GetValue (@DocId INT)
RETURNS TABLE
AS
RETURN
(SELECT T1.AssetId, T1.DocId, T2.Title
FROM Table1 T1
JOIN Table2 T2
ON T1.DocId = T2.DocId
WHERE T1.DocID = @DocId
)
GO
--I was messing with this code, but its not working, I want to be able to make Table1 Title a computed column based on Table 2 Title column and documentId
ALTER TABLE dbo.Table1
ADD Title AS dbo.GetValue(Title)
I get errors.
It seems you are new around here. Welcome!!!
Posting things like "I get errors." is really not very helpful. What are the errors?
I see far more wrong what you have here than roryp suggested. You do in fact have a datatype mismatch but there are far more serious issues with the design of what you are doing. Your function returns a table with 3 columns and you are trying to put that into a single column. This is never going to work like that.
What are you trying to do here?
You should probably take a look at the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 28, 2013 at 11:09 am
Ok, Thanks, I new here and to sql.
I am trying to make Title in table 1 a computed column that will get updated every time the title column in table 2 gets updated.
January 28, 2013 at 11:17 am
mail4bbt (1/28/2013)
Ok, Thanks, I new here and to sql.I am trying to make Title in table 1 a computed column that will get updated every time the title column in table 2 gets updated.
We can help but you have to first help us. Please read the link I previously suggested. Without something to work with there is little chance you will get correct answers.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 28, 2013 at 11:33 am
CREATE TABLE [dbo].[Asset](
[AssetId] [int] NOT NULL,
[DocId] [int] NULL,
[Title] [nvarchar](max) NULL,
CONSTRAINT [PK_Asset] PRIMARY KEY CLUSTERED
(
[AssetId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[Doc](
[DocId] [int] NOT NULL,
[Title] [nvarchar](max) NULL,
CONSTRAINT [PK_Doc] PRIMARY KEY CLUSTERED
(
[DocId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
INSERT INTO [Doc]([Title])
VALUES('Recovery');
INSERT INTO [Doc]([Title])
VALUES('Disaster');
INSERT INTO [Doc]([Title])
VALUES('Pending');
INSERT INTO [Doc]([Title])
VALUES(NULL);
INSERT INTO [Asset]([DocId], [Title])
VALUES(2, NULL);
January 28, 2013 at 11:35 am
I put sample values in the asset table just to see what I meant
January 28, 2013 at 12:19 pm
The sample you posted won't work because there is no DocId specified on the inserts into Doc.
I need to understand the relationship between these two tables. The way you have them defined is denormalized. Do you have an Asset that can have any number of Docs? Is that what you are trying to do here?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply