CODEWITHSUNDEEP
sql-server
Zanam
Zanam

Reputation: 4807

SQL necessity of temp table for this situation

I am using Microsoft SQL server.

I often run into this kind of code:

select (a*5 + a*4 + a*3) as a1 --some complicated and very long manipulation of column 'a'
into #Temp1
from Table1

select a1, case when a1 > 5 then 1 else 0 end as myResult
from #Temp1

I have tried using:

select (a*5 + a*4 + a*3) as a1, case when a1 > 5 then 1 else 0 end as myResult
from Table1

and it gives error as invalid column name for 'a1'. I understand this but for a situation like mine is there a way to avoid creating a #Temp1 step?

Edit: I am aware of such coding structure as:

select (a*5 + a*4 + a*3) as a1, case when (a*5 + a*4 + a*3) > 5 then 1 else 0 end as myResult
from Table1

For a very convoluted manipulation on column 'a' the code becomes unreadable.

Upvotes: 1

Views: 38

Answers (2)

manderson
manderson

Reputation: 891

You can easily put the calculation in your select two times but I reread your question and realized that you said the calculation was a little more advanced that this, so I would go with James Z's answer.

select (a*5 + a*4 + a*3) as a1, case when (a*5 + a*4 + a*3) > 5 then 1 else 0 end as myResult
from Table1

Upvotes: 0

James Z
James Z

Reputation: 12317

You can't refer columns that you made in the same select statement, but you can do for example something like this:

select a1, case when a1 > 5 then 1 else 0 end as myResult
from (
  select (a*5 + a*4 + a*3) as a1 --some complicated and very long manipulation
  from Table1
) X

You can do also similar things with CTEs or using outer apply to create new columns with select.

Upvotes: 4

Related Questions