computed column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

computed column

Author

Topic

divyaram
Posting Yak Master

180 Posts

Posted - 2012-04-23 : 05:07:29

HI all,

I have table in that i want to update the table's one column with calculation,

i have table structure like this

Table_1

Tablename column_name caluclation

Tab1 Col1
Tab2 Col2
Tab3 Col3

for calculation column

select
distincthp.ParameterId
from
MasterTable ml,
Table2 al,
Table3 hp
where
hp.DBName= al.DBName
and al.ProdTableName=hp.ProdTableName
and al.ProdColumn=hp.ProdColumn
and ml.DB=hp.DBName
and ml.ProdTable=hp.ProdTableName
and ml.ProdColumn=hp.ProdColumn
and hp.ParamStatus='ACTIVE' and
al.ParameterName IS NOT NULL
and ml.tempcolumn='col1'
like this it has calculate for col2 , col3 .... coln

Regards,
Divya

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-23 : 07:28:10

Please post the DDL of all your tables, some readily consumable sample data and the expected result set. Without this its really hard to understand what you are expecting.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-23 : 07:28:54

Do you want to store the query for each row, or the results of the query?

If it is the query, seems simple enough - declare the column as a varchar(max) and store the query as a string.

If it is the result of the query that you want to store, first thing that comes to mind is, what do you want to do when the result of the query returns more than one row?

All that aside, while I am not able to put my finger on, the thought of storing and/or running such dynamic queries against a database scares me. If you can describe the business problem that you are trying to solve, some of the experts on this forum may be able to suggest better alternatives.

divyaram
Posting Yak Master

180 Posts

Posted - 2012-04-23 : 08:14:47

Hi Sunita thanks for the reply....

actually in column's like col1 , col2
I am having calculation like
case statements or bracketed caluclation like(A+B*0.2)
From this i have to take A from col1 from table1 and value of A in table2 will be different like UF-A, for B it will be UF-B

SO i have to take A's value from table2 and update in this Table1 like (UF-A+UF-B*0.2)

quote:
Originally posted by sunitabeck

Do you want to store the query for each row, or the results of the query?

If it is the query, seems simple enough - declare the column as a varchar(max) and store the query as a string.

If it is the result of the query that you want to store, first thing that comes to mind is, what do you want to do when the result of the query returns more than one row?

All that aside, while I am not able to put my finger on, the thought of storing and/or running such dynamic queries against a database scares me. If you can describe the business problem that you are trying to solve, some of the experts on this forum may be able to suggest better alternatives.

Regards,
Divya

divyaram
Posting Yak Master

180 Posts

Posted - 2012-04-23 : 08:32:20

Hi,
its like partial update....

case when A then X
when B then Y
else ''
end;

the result should be
case when UF-A then UF-x
when UF-B then UF-Y
else''
end

here once problem like i can't even take substring...

Regards,
Divya

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-23 : 08:52:17

I am only vaguely following what you are describing. It may be helpful if you can post the DDL for the tables along with some sample data in each of the tables in a form that can be run by someone else to generate the tables and data. If you are not able to do that, at least, post the data sort of like this.


REFERENCE TABLE DATA
Tablename   ColumName   Calculation
TAB1         weight       weight*2
TAB2         height       height*height


TAB1
Id  weight
1    110
2    111
3    112

TAB2
Id  height
1    62
2    63
3    64

divyaram
Posting Yak Master

180 Posts

Posted - 2012-04-23 : 10:02:11

Reference Data
Table1

Table_name Column_name Calculation

Tab1 case when A then X
when B then Y
else ''
end;

tab2 (A+B+C)
tab3 D*0.45

Table2

columnname History

A UF_A
B UF_B
C UF_C
X UF_X
Y UF_Y

Have to update Table 1 like

Table_name Column_name Calculation

Tab1 case when A then X case when UF_A then UF_X
when B then Y when UF_B then UF_Y
else '' else ''
end; end;

tab2 (A+B+C) (UF_A+UF_B+UF_C)
tab3 D*0.45 UF_D*0.45

quote:
Originally posted by sunitabeck

I am only vaguely following what you are describing. It may be helpful if you can post the DDL for the tables along with some sample data in each of the tables in a form that can be run by someone else to generate the tables and data. If you are not able to do that, at least, post the data sort of like this.

REFERENCE TABLE DATA
Tablename ColumName Calculation
TAB1 weight weight*2
TAB2 height height*height

TAB1
Id weight
1 110
2 111
3 112

TAB2
Id height
1 62
2 63
3 64

Regards,
Divya

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-23 : 11:36:57

Can you post it with code tags, so the formatting is preserved? You can do it by inserting a [code] and a [/code]. For example:


[code]
SELECT TOP 10
   COL1
FROM
   MYTABLE
[/code]

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-24 : 00:36:38

quote:
Originally posted by sunitabeck

I am only vaguely following what you are describing. It may be helpful if you can post the DDL for the tables along with some sample data in each of the tables in a form that can be run by someone else to generate the tables and data. If you are not able to do that, at least, post the data sort of like this.

REFERENCE TABLE DATA
Tablename ColumName Calculation
TAB1 weight weight*2
TAB2 height height*height

TAB1
Id weight
1 110
2 111
3 112

TAB2
Id height
1 62
2 63
3 64

Please post the data like this.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

divyaram
Posting Yak Master

180 Posts

Posted - 2012-04-24 : 04:45:52

Reference Data
Table1


Table_name	|Column_name				|Calculation
-------------------------------------------------------------------
Tab1		         |case when A then X      
		         |when B then Y
		         |else ''
		         |end;
---------------------------------------------------
tab2 		|(A+B+C)
----------------------------------------------------
tab3		|D*0.45


Table2

columnname    | History
------------------------------------------
A				|UF_A
B				|UF_B
C				|UF_C
X				|UF_X
Y				|UF_Y

Have to update Table 1 like


Table_name	Column_name				Calculation

Tab1		|case when A then X     |case when UF_A thenUF_X     
		|when B then Y		|when UF_B then UF_Y
		|else ''		|else ''
		|end;			|end; 
			

tab2 		|(A+B+C)			|(UF_A+UF_B+UF_C)
tab3		|D*0.45				|UF_D*0.45

Regards,
Divya

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-24 : 07:33:11

I can't think of a reliable way to do this if the place holders are single letters like A, B, C etc. If you are able to use placeholders that would be unique, all you may need is a set of replace statements.

Since I know nothing at all about the business problem you are trying to solve, this may come across as a silly question: Isn't there some way in which you can design the system so you don't have to store all these disparate query fragments, substitute the variables names and evaluate them?

Seems like there is way too much room for someone to make mistakes and mess up the system, or even worse, someone malicious to mess with it and do real damage.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources