Using Results from one column to calculate another column

Question

Using Results from one column to calculate another column

pvong

SSC Eights!

Points: 832
More actions
January 4, 2013 at 4:06 pm

#286985

Doing this in SQL 2008.
Is it possible to use the results of one column to calculate another column? Example:
Select .. VERY LONG FORMULA as Results1
I need another column with a CASE so..
CASE Result1 < 100 Then something as Results2
Is it possible to do something like this or do I have to do the whole long formula again like...
CASE VERY LONG FORMULA < 100 Then something as Result2
I already have Results1 and it's silly to do it again just to calculate Results2.
God I hope this isn't too confusing.
Thanks in advance.
Phil

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Vedran Kesegic SSCrazy Eights Points: 9302 More actions · Answer 1

If you make one result as persistent calculated column, and other as non-persistent calculated column, than maybe.

If you want to be sure, instead of calculated fields use trigger to calculate and store both values, but that may not be as performant.

Other idea is to make result1 as persistent calculated field and then view on top of that table that has calculation for result2.

Or, do it completely non-persistent, with view that has nested subquery or cross apply or cte that would guarantee the order of calculations and reusage of result from previous level. My favourite would be completely persistent solution, or cross apply from non-persistent world, depending on read-write ratio of that table and cost of formula calculation.

Hth

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths

vinu512 SSCoach Points: 15729 More actions · Answer 2

I already have Results1 and it's silly to do it again just to calculate Results2.

I am assuming that Results1 is a data set that you are selecting a table.

I agree with Joe on this one. You can user a CTE or a derived table to calcualte Results2 on the basis of Results1.

Here are a few links on how to use them:

Using Derived Tables[/url]

Using CTE

Hope this helps.

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 3

Use CROSS APPLY.

SELECT

x.Results1,

CASE WHEN x.Results1 > 42 THEN ...

FROM ...

CROSS APPLY (SELECT... VERY LONG FORMULA as Results1) x

WHERE x.Results1 = something

Give it a shot. Without sight of your current query, it's difficult to say more.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

rajubaps SSC Enthusiast Points: 108 More actions · Answer 4

if the resultset is not very big then the simplest way of doing is,

SELECT *, CASE Result1 < 100 Then something as Results2

FROM (Select .. VERY LONG FORMULA as Results1) t1

Raju

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 5

CELKO (1/4/2013)
h) The ORDER BY clause is part of a cursor, not a query. The result set is passed to the cursor, which can only see the names in the SELECT clause list, and the sorting is done there. The ORDER BY clause cannot have expression in it, or references to other columns because the result set has been converted into a sequential file structure and that is what is being sorted.

Not that it's a particularly useful example of a sort but, just to prove how wrong virtually everything in the statement above can actually be in T-SQL, please consider the following...

SELECT service_broker_guid

FROM sys.Databases

ORDER BY CASE

WHEN database_id < = 4

THEN N' ' + name

ELSE CAST(create_date AS NVARCHAR(128))

END

Note that nothing in the SELECT list is referenced by the ORDER BY, that the ORDER BY does, in fact have several expressions in it and is really just one big expression to begin with, and that it not only references columns outside the SELECT list but conditionally sorts based on the content of two entirely different columns.

CELKO (1/4/2013)
a) Effectively materialize the CTEs in the optional WITH clause. CTE's come into existence in the order they are declared so only backward references are alllowed.
{snip}
As you can see, things happen "all at once" in SQL, not "from left to right" as they would in a sequential file/procedural language model.

As you can see in your own writing, "It Depends". Some things don't actually happen "all at once" in SQL. In fact, even an update on a single column doesn't happen all at once. It happens just like procedural code... one row at a time, one page at a time.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 6

Sorry... double post removed...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101105 More actions · Answer 7

If all the columns are from a single table, you definitely should created a computed column that has the computation: that way, the definition is in only one place. You do not have to persist it, although you could -- you can base that on how often you use it vs how much overhead it really is to calculate vs the bytes needed to store it.

Whether persisted or not, you can use it in a SELECT just like any other column. For example:

ALTER TABLE dbo.yourtablename

ADD Results1 AS Col1 * 1.57 / Col2 * CASE WHEN Col3 = 'A' THEN Col4 ELSE Col5 END

Then your query can be:

SELECT

Results1, Colx / Results1 AS ...

FROM dbo.yourtablename

WHERE

Results1 >= 9.75

GROUP BY

Results1

ORDER BY

Results1

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ScottPletcher SSC Guru Points: 101105 More actions · Answer 8

CELKO (1/4/2013)
Think about what a confused mess this statement is in the SQL model.
SELECT f(c2) AS c1, f(c1) AS c2 FROM Foobar;
That is why such nonsense is illegal syntax.

Your claim is nonsense. I don't see what's illegal about that general syntax in SQL Server, viz:

SELECT RTRIM(c2) AS c1, RTRIM(c1) AS c2

FROM (

SELECT 'Y' AS c1, 'Z' AS c2 UNION ALL

SELECT 'C' AS c1, 'D' AS c2 UNION ALL

SELECT 'A' AS c1, 'B' AS c2

) AS test_data

ORDER BY c1

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

ScottPletcher (1/7/2013)
If all the columns are from a single table, you definitely should created a computed column that has the computation: that way, the definition is in only one place. ...[/code]

+1

Otherwise, use CROSS APPLY, keeping the definition in only one place in the query. If the calculation is used in many queries, consider constructing an inline "row-valued" function.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden