Using calculated cols in other calculated cols

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

SQL Server 2005 Forums

Transact-SQL (2005)

Using calculated cols in other calculated cols

Author

Topic

bwechner
Starting Member

2 Posts

Posted - 2012-06-07 : 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)

17689 Posts

Posted - 2012-06-07 : 23:02:09

[code]
SELECT, A, B, B+1 AS C
FROM
(
SELECT A, A+1 AS B FROM TABLE
) T
[/code]

or use CTE http://msdn.microsoft.com/en-us/library/ms175972.aspx

KH
[spoiler]Time is always against us[/spoiler]

bwechner
Starting Member

2 Posts

Posted - 2012-06-08 : 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
[spoiler]Time is always against us[/spoiler]

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)

17689 Posts

Posted - 2012-06-08 : 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
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources