Posted by Hugo Kornelis on 10/01/72 11:42

On 14 Mar 2006 12:29:25 -0800, lukster@gmail.com wrote:

>Hello There,
>
>I'm trying to create a view that has calculations dependent on
>calculations, where the problem resides is that each time I make a
>calculation I must create an intermediate view so I can reference a
>previous calculation.
>
>for example lets say I have my_table that has columns a & b. now I want
>a view that has a & b, c = a + b, and d = c + 1.
>
>this is grossly simplified, the calculations I actually use are fairly
>complex and copying / pasting them is out of the question.
>
>so what I have is my_view_a which makes column c, and my my_view_final
>which makes column d (however, in my real application I have 5 of these
>views, a/b/c/d/e/)
>
>is there anyway I can consolidate all these views into one? I was
>thinking of using a stored procedure with temp tables or something
>along those lines.
>
>I just which I can use the aliases that I create for c in d in one
>step.
>
>any insight would be greatly appreciated.

Hi lukster,

You can use derived tables instead of views:

SELECT a, b, c, c + 1 AS d
FROM (SELECT a, b, a + b AS c
FROM SomeTable
WHERE .....
) AS Der
WHERE .....

You can nest this if yoou need to.

Another technique is to repeat the calculation:

SELECT a, b, a + b AS c, (a + b) + 1 AS d
FROM SomeTable
WHERE .....

--
Hugo Kornelis, SQL Server MVP

[Back to original message]