CODEWITHSUNDEEP
sql-server
Jerome
Jerome

Reputation: 25

SQL: Working With Columns That You've Just Created in SQL Server

Sorry if this question has already been posted, but I'm fairly new to SQL server and can't find the answer I'm looking for. (Maybe I'm just unable to word it properly.. oh well)

So basically, I'm trying to work with a column that's just been created in my query

as a very simple example:

SELECT q.Account, q.Quantity, q.Price,

CASE
    WHEN q.Account = '49661B' THEN (q.Quantity * q.Price)

END AS [USD Total Value],

[USD Total Value] * 1.34 as [CDN Value]

FROM Quote as q

I am trying to use that column I just named [USD Total Value] to convert in CDN as a new column. But I'm getting an error [Invalid column name 'USD Total Value']

This is obvioulsy very basic, but I'd really appreciated some help on that.

Many Thanks!

Upvotes: 0

Views: 1630

Answers (4)

Standin.Wolf
Standin.Wolf

Reputation: 1234

You need to do it as a subquery

select *,

[USD Total Value] * 1.34 as [CDN Value]

from (

      SELECT q.Account, q.Quantity, q.Price,

         CASE
         WHEN q.Account = '49661B' THEN (q.Quantity * q.Price)

         END AS [USD Total Value]

      FROM Quote as q

)x

This should give you what you require

UPDATE

As UUerdo said

Aliases from SELECT expressions may not be referenced anywhere within the query (at that level... aliases in the SELECTs of a subquery may be referenced in the outer query)

Upvotes: 2

user3304735
user3304735

Reputation: 41

One of my favorites would be using the CTE. The only drawback it cannot be used recursively 

;WITH CTE as
(
SELECT q.Account, q.Quantity, q.Price,

CASE
    WHEN q.Account = '49661B' THEN (q.Quantity * q.Price)

END AS [USD Total Value]

FROM Quote as q
)

Then use the CTE

select *, [USD Total Value] * 1.34 as [CDN Value] 

FROM CTE;

Upvotes: 2

user1932600
user1932600

Reputation: 57

As Standin.Wolf mentioned about Temp tables I can show you how to go about that

Use your original query and put it into a temp table first

SELECT q.Account, q.Quantity, q.Price,

CASE
    WHEN q.Account = '49661B' THEN (q.Quantity * q.Price)

END AS [USD Total Value]

Into #temp

FROM Quote as q

Then use the column you created for further calculations from the temp table

select *, [USD Total Value] * 1.34 as [CDN Value] 

FROM #temp;

Upvotes: 2

Lukasz Szozda
Lukasz Szozda

Reputation: 175826

SQL Server does not allow to refer alias at same level:

SELECT q.Account, q.Quantity, q.Price,s.[USD Total Value]
      ,s.[USD Total Value] * 1.34 as [CDN Value]
FROM Quote as q
CROSS APPLY(SELECT CASE WHEN q.Account = '49661B' THEN (q.Quantity * q.Price)END
           ) AS s([USD Total Value]);

If you don't like the idea of wrapping everything with outerquery, then CROSS APPLY(LATERAL) is the way to go.

Upvotes: 1

Related Questions