| Author |
 Topic  |
|
|
divyaram
Posting Yak Master
India
177 Posts |
 Posted - 04/23/2012 : 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
India
227 Posts |
Posted - 04/23/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 04/23/2012 : 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
India
177 Posts |
Posted - 04/23/2012 : 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
India
177 Posts |
Posted - 04/23/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 04/23/2012 : 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
India
177 Posts |
Posted - 04/23/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 04/23/2012 : 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
India
227 Posts |
Posted - 04/24/2012 : 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
India
177 Posts |
Posted - 04/24/2012 : 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 |
Edited by - divyaram on 04/24/2012 04:49:34 |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 04/24/2012 : 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. |
|
|
| |
Topic |
|
computed column
