Can I define a new column and use it in the same query?
For example,
Select
student, age + 10 as trueage,
case when trueage < 25 then 'False' else 'True' end
From
class
Thank you in advance for helping!
3 Answers
CROSS APPLY would be handy here. Here is a nice article showing many uses of it.
Select
student
,trueage
,case when trueage < 25 then 'False' else 'True' end
From
class
CROSS APPLY
(
SELECT age + 10 as trueage
) AS CA
It is mostly useful when you have complex formulas with several levels, something like this:
Select
student
,trueage
,AgeFlag
From
class
CROSS APPLY
(
SELECT age + 10 as trueage
) AS CA1
CROSS APPLY
(
SELECT case when trueage < 25 then 'False' else 'True' end AS AgeFlag
) AS CA2
Comments
If you consider using a subquery "same query" then yes.
select student,
trueAge,
case when trueAge < 25 then 'false' else 'true' end as someColumn
from (
select student, age + 10 as trueAge
from table
) thingy
There are likely other ways to do this as well, this is only one of them.
Comments
Something like:
Select student, age + 10 as trueage,
case when (age + 10) < 25 then 'False' else 'True' end
from class
Comments
CROSS APPLY. One of them is to "Introduce New Columns".