How to use one computed column in another computed column

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

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 10-Sep-12 1:55am

madhuri@mumbai

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

prashant_khairnar · Answer 1 · 2012-09-10T03:56:00

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

__TR__ · Answer 2 · 2012-09-10T02:01:00

Solution 1

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]

Posted 10-Sep-12 2:01am

__TR__

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,,,

__TR__ 10-Sep-12 9:06am

I am sorry i did not understand your question.

Devang Vaja · Answer 3 · 2012-09-10T22:52:00

Solution 3

use
[total] AS ((((((([registration]+[contact])+[property])+([income]+[expense]+[asset]+[liability])+[qa])+[hardship])+[suppdoc])+[rvsub])
this solution Miss Desale Ji...

Posted 10-Sep-12 22:52pm

Devang Vaja

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