Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Using Results from one column to calculate another column Expand / Collapse
Author
Message
Posted Friday, January 4, 2013 4:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 11:29 AM
Points: 7, Visits: 60
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
Post #1403155
Posted Friday, January 4, 2013 5:58 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 5:27 AM
Points: 369, Visits: 1,214
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
Post #1403173
Posted Friday, January 4, 2013 6:44 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 10:38 AM
Points: 1,945, Visits: 3,004
I already have Results1 and it's silly to do it again just to calculate Results2.


Here is how a SELECT works in SQL ... at least in theory. Real products will optimize things, but the code has to produce the same results.

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. A CTE can be recursive. Think of them as VIEWs that exist only in the scope of the query. In practice, if they are used once then they are implemented as an in-line macro.

b) Start in the FROM clause and build a working table from all of the joins, unions, intersections, and whatever other table constructors are there. The <table expression> AS <correlation name> option allows you give a name to this working table which you then have to use for the rest of the containing query. Ther are UNIONB, INTERSECT and EXCEPT set construtors, LATERAL tables, table-valued funcitosn and all kinds of things happening in here.

c) Go to the WHERE clause and remove rows that do not pass criteria; that is, that do not test to TRUE (i.e. reject UNKNOWN and FALSE). The WHERE clause is applied to the working set in the FROM clause.

d) Go to the optional GROUP BY clause, partiton the original table into groups and reduce each grouping to a *single* row, replacing the original working table with the new grouped table. The rows of a grouped table must be only group characteristics: (1) a grouping column (2) a statistic about the group (i.e. aggregate functions) (3) a function or constant(4) an expression made up of only those three items. The original table no longer exists and you cannot reference anything in it (this was an error in early Sybase products).

e) Go to the optional HAVING clause and apply it against the grouped working table; if there was no GROUP BY clause, treat the entire table as one group.

f) Go to the SELECT clause and construct the expressions in the list. This means that the scalar subqueries, function calls and expressions in the SELECT are done after all the other clauses are done. The AS operator can also give names to expressions in the SELECT list. These new names come into existence all at once, but after the WHERE clause, GROUP BY clause and HAVING clause have been executed; you cannot use them in the SELECT list or the WHERE clause for that reason.

If there is a SELECT DISTINCT, then redundant duplicate rows are removed. For purposes of defining a duplicate row, NULLs are treated as matching (just like in the GROUP BY).

g) Nested query expressions follow the usual scoping rules you would expect from a block structured language like C, Pascal, Algol, etc. Namely, the innermost queries can reference columns and tables in the queries in which they are contained.

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.

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. In those languages, these two statements produce different results:
READ (a, b, c) FROM File_X;
READ (c, a, b) FROM File_X;

while these two statements return the same data:

SELECT a, b, c FROM Table_X;
SELECT c, a, b FROM Table_X;

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.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1403181
Posted Friday, January 4, 2013 10:21 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:13 AM
Points: 1,127, Visits: 1,594

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

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
Post #1403193
Posted Saturday, January 5, 2013 11:55 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 6:20 AM
Points: 1,095, Visits: 6,671
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.



Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




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
Post #1403307
Posted Sunday, January 6, 2013 8:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 9, 2014 1:00 PM
Points: 2, Visits: 16
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
Post #1403332
Posted Sunday, January 6, 2013 3:18 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:10 AM
Points: 37,102, Visits: 31,657
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1403363
Posted Sunday, January 6, 2013 11:53 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:10 AM
Points: 37,102, Visits: 31,657
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1403427
Posted Monday, January 7, 2013 5:06 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:51 PM
Points: 2,095, Visits: 3,146
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)

"We came in spastic, Like tameless horses /
We left in plastic, As numbered corpses / ...
Remember Charlie, Remember Baker /
They left their childhood On every acre /
And who was wrong? And who was right? /
It didn't matter in the thick of the fight." : the inimitable Mr. Billy Joel, about the Vietnam War
Post #1403921
Posted Monday, January 7, 2013 5:10 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:51 PM
Points: 2,095, Visits: 3,146
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)

"We came in spastic, Like tameless horses /
We left in plastic, As numbered corpses / ...
Remember Charlie, Remember Baker /
They left their childhood On every acre /
And who was wrong? And who was right? /
It didn't matter in the thick of the fight." : the inimitable Mr. Billy Joel, about the Vietnam War
Post #1403923
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse