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.
| Author |
Topic |
|
hmelihkara
Starting Member
2 Posts |
 Posted - 2009-01-14 : 11:24:44
|
| Hi,I have a stored procedure has sub selects and many fields. And returns multiple rows. But also i must make some calculations with the fields for eg like:Field1 = (A + B);Field2 = (A + B) / C;Field3 = ((A + B) / C) * D;when i run query it tooks about 1 mins to complete about these re-calculations. But if i can use variables with the select the pain is over...for eg:i must use:SELECTt1.f1,t1.f2,t1.f3,@V1 = (SELECT ..... FROM t1 where t1.f1 = t2.f1) ,@V2 = (SELECT ..... FROM t2 where t1.f1 = t2.f1),@V1*@V2,(@V1*@V2)*5/@V2FROM t2ORDER BY t1.f1 ASCIn every row... @V1 AND @V2 is calculated so in order to write:(SELECT ..... FROM t1 where t1.f1 = t2.f1) * (SELECT ..... FROM t2 where t1.f1 = t2.f1)i can write@V1*@V2but this must also return me all the rows...thanks for replies... |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-14 : 11:57:06
|
| you cant mix up assignments with column retrieval in same select. seems like what you need is set of computed columns. |
 |
|
|
hmelihkara
Starting Member
2 Posts |
Posted - 2009-01-14 : 13:31:50
|
| Can I do it on the fly? I can't add columns to the table. Can I make calculated fields in the query?or why can't we use the subquery as a fieldname for eg:(select number from table where....) as myfield,(myfield * 5) as mysecondfieldthis gives error, is there a way to override this without writing the subquery again like this:((select number from table where....) * 5 ) as mysecondfield...i don't want to re-calculate all...thanks for replies again... |
|
|
|
|
|
|
USING VARIABLE IN SELECT AND RETURN ALL ROWS