Click here to Skip to main content
15,917,875 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
hello

I want to create table below table in which i have two computed column ,but there are some error msg while creating table,,,,,
SQL
CREATE TABLE [tblcaseprogress_old](
    [Et_id] [int] NOT NULL,
    [u_id] [int] NOT NULL,
    [registration] [int] NULL,
    [contact] [int] NULL,
    [property] [int] NULL,
    [expense] [int] NULL,
    [income] [int] NULL,
    [asset] [int] NULL,
    [liability] [int] NULL,
    [qa] [int] NULL,
    [hardship] [int] NULL,
    [suppdoc] [int] NULL,
    [rvsub] [int] NULL,
    [cobor] [int] NULL,
    [empinfo] [int] NULL,
    [financial] AS ((([income]+[expense])+[asset])+[liability]),
    [total]  AS ((((((([registration]+[contact])+[property])+[financial])+[qa])+[hardship])+[suppdoc])+[rvsub])
) ON [PRIMARY]


Error:
Computed column 'financial' in table 'tblcaseprogress_old' is not allowed to be used in another computed-column definition.



anything wrong from mine side,plz suggest me...
Posted

Hello,

"computed columns are virtual columns that are not physically stored in the table. Their values are recalculated every time they are referenced in a query. The Database Engine uses the PERSISTED keyword in the CREATE TABLE and ALTER TABLE statements to physically store computed columns in the table. Their values are updated when any columns that are part of their calculation change. By marking a computed column as PERSISTED."

So bottom line is practically its not possible.

Please refer below link for more details : http://msdn.microsoft.com/en-us/library/ms191250%28SQL.105%29.aspx
 
Share this answer
 
Comments
madhuri@mumbai 10-Sep-12 10:22am    
thanks from my side 5pts
try
SQL
CREATE TABLE [tblcaseprogress_old](
    [Et_id] [int] NOT NULL,
    [u_id] [int] NOT NULL,
    [registration] [int] NULL,
    [contact] [int] NULL,
    [property] [int] NULL,
    [expense] [int] NULL,
    [income] [int] NULL,
    [asset] [int] NULL,
    [liability] [int] NULL,
    [qa] [int] NULL,
    [hardship] [int] NULL,
    [suppdoc] [int] NULL,
    [rvsub] [int] NULL,
    [cobor] [int] NULL,
    [empinfo] [int] NULL,
    [financial] AS ((([income]+[expense])+[asset])+[liability]),
    [total]  AS ((((((([registration]+[contact])+[property])+((([income]+[expense])+[asset])+[liability]))+[qa])+[hardship])+[suppdoc])+[rvsub])
) ON [PRIMARY]
 
Share this answer
 
Comments
madhuri@mumbai 10-Sep-12 8:06am    
great,and thanks for ur rply,
I have already apply this solution from my side,
but purpose of asking this,can this possible which i have created table structure above(in my question),
__TR__ 10-Sep-12 8:17am    
I don't understand "can this possible which i have created table structure above".
What is possible ?
madhuri@mumbai 10-Sep-12 8:37am    
please read my question carefully,i have asked that,
"How to use one computed column in another computed column" in sqlserver.
__TR__ 10-Sep-12 8:39am    
You can not use one computed column inside other computed column.
madhuri@mumbai 10-Sep-12 8:50am    
if i need to do in this way then what solution u have to suggest me,,,
use
[total] AS ((((((([registration]+[contact])+[property])+([income]+[expense]+[asset]+[liability])+[qa])+[hardship])+[suppdoc])+[rvsub])
this solution Miss Desale Ji...
 
Share this answer
 
Comments
madhuri@mumbai 11-Sep-12 6:05am    
Thanks for rply
and this solution alredy posted,i expect other than above solutions,
and what is this ,Miss Desale ji.. how u know?
Devang Vaja 11-Sep-12 6:22am    
i Know u...Open Your FB der will Be MY request

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900