| Author |
 Topic  |
|
|
bwechner
Starting Member
Australia
2 Posts |
 Posted - 06/07/2012 : 22:53:38
|
Wish I had a better way of describing it, but I'm lacking the jargon. Still here's a pro-forma of what I want to do and it dosn't work and so I wonder if there is a way to do it:
SELECT A, A+1 AS B, B+1 AS C FROM TABLE
The reference to column B fails.
Save yourself the breath if you're thinking to recommend:
SELECT A, A+1 AS B, A+2 AS C FROM TABLE
as a) I'm not totally stupid and b) I'm looking at calculations that are let's just say complicated and repeated. So perhaps I should better write something like this to demonsrate:
SELECT A, Calc1 AS B, Calc2 AS C, CASE WHEN A is NULL THEN B/C ELSE C/B END AS D FROM TABLE
where Calc1 and Calc2 are themselves very large calculations and the Calculation for column D may itself also be very complicated and large.
Also, yes, I'd like to do this on the fly if possible, not (as I suspect I could) generate a temporary table with the intermediate ccalculations in it as coluns and then select on the temporary table to do the meta calculation. Certainly possible, but is there something more elegant. A way of using the SELECT statements own output columns in calculating other output columns?
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
|
|
bwechner
Starting Member
Australia
2 Posts |
Posted - 06/08/2012 : 00:27:13
|
quote:
Originally posted by khtan
SELECT, A, B, B+1 AS C
FROM
(
SELECT A, A+1 AS B FROM TABLE
) T
or use CTE http://msdn.microsoft.com/en-us/library/ms175972.aspx
KH
Time is always against us
Excellent! Awesome in fact. Nested tables I was aware of and have sued extensibvely but forgot to mention above. Oddly as it would be one of the more elegant solutions but still not an ideal one for me. CTE is new took me, but looks also not to be ideal.
The constraint that negatively influences the appeal of either solution is that I'm using views in writtein in the Visual Studio design tools and would target a syntax it can cope with first. Not a hard constraint, a preference, as it makes it easier for downstream staff to work with these views later and so on. The designer is simplifying. And among its contsraints is, it has poor support of nested tables and probably none for CTE, but I'll have little play as prcticing CTE would have benefit anyhow.
What I was forelornley hoping for was a simpler in-line syntax perhaps like:
SELECT A, A+1 AS B, this.B+1 AS C FROM TABLE
but perhaps not. |
 |
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 06/08/2012 : 01:18:30
|
another way is to create the computed column
alter table [TABLE] add B as A + 1
SELECT A, B FROM TABLE
KH
Time is always against us
|
|
|
| |
Topic |
|
|
|
Using calculated cols in other calculated cols
