Use a calculated value in multiple places in a query

totig

I am building a query which returns a few calculated values. Some of these are as simple as ColA - ColB, while others are far more complex - but are often based on the result of a previous result. My question is, how can I reuse these fields that I have calculated? A lot of the time that I do these calculations, I also need to exclude some values in the WHERE clause, based on these answers. Example: SELECT ColA - ColB [Result] FROM Table WHERE (ColA - ColB) > 0 How could I calculate ColA - ColB just once - and use the result of that in the where clause or another column? Thanks in advance

Wendelius

One way is to use inline views, like:

SELECT alias1.Result
FROM (SELECT (ColA - ColB) AS Result
FROM Table) alias1
WHERE alias1.result > 0

The need to optimize rises from a bad design.My articles[^]

totig

Thanks for the reply. I was just kind of hoping there was a way to store the value in a temp variable on a row by row basis, but using inline views does the trick as well - so thanks.

Ben Fair

In SQL Server 2005+ you can use CTEs (Common Table Expressions) to do the same kind of thing:

WITH Results AS
(
SELECT ColA - ColB [Result]
FROM TABLE
)
SELECT *
FROM Results
WHERE Result > 0

Or with a temp table like so:

SELECT ColA - ColB [Result]
INTO #temp
FROM TABLE

SELECT *
FROM #temp
WHERE Result > 0

DROP TABLE #temp

Or with a table variable like so:

DECLARE @temp TABLE (Result int)

INSERT @temp
SELECT ColA - ColB [Result]
FROM TABLE

SELECT *
FROM @temp
WHERE Result > 0

I prefer to use the CTE because I think it's less code and easily readable.

Keep It Simple Stupid! (KISS)

Wendelius

That's true and leads to the same result. Writing inlines is just a (bad) habit that always comes first in mind. Guess it's because I've written them over fifteen years so I believe it's like teaching an old dog... :)

The need to optimize rises from a bad design.My articles[^]

Wendelius

toticow wrote:

I was just kind of hoping there was a way to store the value in a temp variable on a row by row basis

That won't be possible (and wouldn't be efficient) since sql is set based language. What you were hoping for would be record based and this would be close to cursor handling (and of course could be implemented in an procedure using cursors). But you don't want to use cursors since (as Mycroft in this forum said) they are evil :) Also have a look at other set based variations Ben Fair posted.

toticow wrote:

so thanks

You're welcome.

The need to optimize rises from a bad design.My articles[^]

Ben Fair

Well, I can relate to that!

Keep It Simple Stupid! (KISS)

PIEBALDconsult

Mika Wendelius wrote:

comes first in mind

Probably more portable too?

Wendelius

PIEBALDconsult wrote:

Probably more portable too?

I'd say so. Many DBMS support inline views.

The need to optimize rises from a bad design.My articles[^]