April 12, 2015 at 5:44 pm
Hi
I am new to SQL Programming. I am learning the basics. I am trying to create a simple query like this -
SELECT
Column_1,
Column_2,
Column_3,
10*Column_1 AS Column_4,
10*Column_2 AS Column_5,
-- I am not being able to understand how to do this particular step Column_1*Column_5 As Column_6
FROM Table_1
First 3 Columns are available within the Original Table_1
The Column_4 and Column_5 have been created by me, by doing some Calculations related to the original columns.
Now, when I try to do FURTHER CALCULATION on these newly created columns, then SQL Server does not allows that.
I was hoping that I will be able to use the Newly Created Columns 4 and 5 within this same query to do further more calculations, but that does not seems to be the case, or am I doing something wrong here ?
If I have to create a new column by the name of Column_6, which is actually a multiplication of Original Column_1 and Newly Created Column_5 "I tried this - Column_1*Column_5 As Column_6", then what is the possible solution for me ?
Please suggest the various options possible for doing this.
I have tried to present my problem in the simplest possible manner. The actual query has many original columns from Table_1 and many Calculated columns that are created by me.And now I have to do various calculations that involve making use of both these type of columns.
If I have not been able to make myself clear then please tell and I will give more examples.
Thanks a lot for any help.
April 12, 2015 at 5:50 pm
Hi,
You cant reference a column alias in the same select statement it was created.
You will either need to do Column1*10*column2 as Column6
or wrap it in a cte first
with cte as (
SELECT
Column_1,
Column_2,
Column_3,
10*Column_1 AS Column_4,
10*Column_2 AS Column_5
)
select *, column1*column5 as Column6
from cte
April 13, 2015 at 12:53 am
matak (4/12/2015)
Hi,You cant reference a column alias in the same select statement it was created.
You will either need to do Column1*10*column2 as Column6
or wrap it in a cte first
with cte as (SELECT
Column_1,
Column_2,
Column_3,
10*Column_1 AS Column_4,
10*Column_2 AS Column_5
)
select *, column1*column5 as Column6
from cte
Thank you so much for providing the solution matak. That works perfectly.
For other newbies like me, who might be wondering why the original query does not work automatically without using any CTE method etc. the explanation is given in this article -
http://sqlmag.com/blog/tip-apply-and-reuse-column-aliases
SQL is a language with many unique aspects. One of those is the fact that the logical order in which the various query clauses are evaluated is different than the keyed-in order. The keyed-in order of a query’s clauses is:
a. SELECT
b. FROM
c. WHERE
d. GROUP BY
e. HAVING
f. ORDER BY
But the logical query processing order is:
1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT -- column aliases created here
6. ORDER BY
Due to this special design a column alias assigned in the SELECT phase is not visible to preceding logical query processing phases.
Thanks and regards
April 13, 2015 at 3:34 am
@ Moderators
I made a reply to the above post, few hours ago. But that post seems to have become invisible now !
Can you please make that post visible, or I will have to post the reply again ?
Thanks a lot
----- EDIT -----
Since my reply was not posted by the moderators, I am typing that reply again here.
matak (4/12/2015)
Hi,You cant reference a column alias in the same select statement it was created.
You will either need to do Column1*10*column2 as Column6
or wrap it in a cte first
with cte as (SELECT
Column_1,
Column_2,
Column_3,
10*Column_1 AS Column_4,
10*Column_2 AS Column_5
)
select *, column1*column5 as Column6
from cte
Thank you so much for providing the solution matak. That works perfectly.
For other newbies like me, who might be wondering why the original query does not work automatically without using any CTE method etc. the explanation is given in this article -
http://sqlmag.com/blog/tip-apply-and-reuse-column-aliases
SQL is a language with many unique aspects. One of those is the fact that the logical order in which the various query clauses are evaluated is different than the keyed-in order. The keyed-in order of a query’s clauses is:
a. SELECT
b. FROM
c. WHERE
d. GROUP BY
e. HAVING
f. ORDER BY
But the logical query processing order is:
1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT -- column aliases created here
6. ORDER BY
Due to this special design a column alias assigned in the SELECT phase is not visible to preceding logical query processing phases.
Thanks and regards
April 13, 2015 at 5:11 am
I sometimes use OUTER APPLY to avoid repeating a formula instead of using a CTE, but I'm not sure which method is more efficient. For example:
SELECT
Column_1,
Column_2,
Column_3,
10*Column_1 AS Column_4,
OA.Column_5,
Column_1*OA.Column_5 As Column_6
FROM Table_1
OUTER APPLY (SELECT Column_5=10*Table_1.Column_2) OA
April 13, 2015 at 2:49 pm
You can also do simple wraps/nests right in the source query. Think of it as an action on the data in the table that you've wrapped (in this case, table a is already extracted, but doesn't have the extra math done, so extract that data as you need it, wrap it, do new maths). As in:
SELECT a.*, (a.Column_1*a.Column_5) as Column_6
FROM (
SELECT
Column_1,
Column_2,
Column_3,
10*Column_1 AS Column_4,
10*Column_2 AS Column_5,
-- I am not being able to understand how to do this particular step Column_1*Column_5 As Column_6
FROM Table_1
) as a
I use these pretty frequently, as they don't seem to harsh the optimizer too bad, and they are easily readable even by SQL noobs.
April 14, 2015 at 6:23 am
gward 98556 (4/13/2015)
I sometimes use OUTER APPLY to avoid repeating a formula instead of using a CTE, but I'm not sure which method is more efficient. For example:
SELECT
Column_1,
Column_2,
Column_3,
10*Column_1 AS Column_4,
OA.Column_5,
Column_1*OA.Column_5 As Column_6
FROM Table_1
OUTER APPLY (SELECT Column_5=10*Table_1.Column_2) OA
Thank you so much gward 98556 for providing this alternative method.
Regards
April 14, 2015 at 6:24 am
andrew.courneya 63072 (4/13/2015)
You can also do simple wraps/nests right in the source query. Think of it as an action on the data in the table that you've wrapped (in this case, table a is already extracted, but doesn't have the extra math done, so extract that data as you need it, wrap it, do new maths). As in:SELECT a.*, (a.Column_1*a.Column_5) as Column_6
FROM (
SELECT
Column_1,
Column_2,
Column_3,
10*Column_1 AS Column_4,
10*Column_2 AS Column_5,
-- I am not being able to understand how to do this particular step Column_1*Column_5 As Column_6
FROM Table_1
) as a
I use these pretty frequently, as they don't seem to harsh the optimizer too bad, and they are easily readable even by SQL noobs.
Wow ! There are so many ways to do this. I was not aware. Thank you so much andrew.courneya 63072 for sharing this approach.
Regards
April 14, 2015 at 6:38 am
For very simple calculations like your example, it's normally fine to simply do the whole calculation in the SELECT part of the query, accepting that there is a little bit of repetition between the column calculations. However, I think if you have a very complex calculation that is involved in multiple calculated columns in the SELECT clause, or in the WHERE clause or even a JOIN clause, then it is nicer to use a CTE or an OUTER APPLY, because then you only have the calculation's definition in one place. This makes the query safer to maintain in future, because there is always a risk that someone might edit the query, and they will find and improve the calculation in the SELECT but not scroll down and see that the same calculation also appeared somewhere else, such as the WHERE clause way down at the bottom, and suddenly the query won't work the way it way intended. Using a CTE or an OUTER APPLY effectively gives you an alias name for the calculation that can be reused in as many places as needed, without writing the whole calculation again.
April 14, 2015 at 9:02 am
gward 98556 (4/14/2015)
For very simple calculations like your example, it's normally fine to simply do the whole calculation in the SELECT part of the query, accepting that there is a little bit of repetition between the column calculations. However, I think if you have a very complex calculation that is involved in multiple calculated columns in the SELECT clause, or in the WHERE clause or even a JOIN clause, then it is nicer to use a CTE or an OUTER APPLY, because then you only have the calculation's definition in one place. This makes the query safer to maintain in future, because there is always a risk that someone might edit the query, and they will find and improve the calculation in the SELECT but not scroll down and see that the same calculation also appeared somewhere else, such as the WHERE clause way down at the bottom, and suddenly the query won't work the way it way intended. Using a CTE or an OUTER APPLY effectively gives you an alias name for the calculation that can be reused in as many places as needed, without writing the whole calculation again.
That makes perfect sense gward. In that situation CTE or OUTER APPLY would be better.
Thanks again.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply