CODEWITHSUNDEEP
sqlsql-server
jcvegan
jcvegan

Reputation: 3170

How to work with calculated columns in SQL Query

I don't know if the title of this question is understood, but what I mean is this.

I have a table of sales and I need to "format" the table adding on query some columns to indicate the total, change rate (which i took form another table) and the total amount rated on other currency.

So my query looks like:

SELECT SalesID,SalesType,SalesDate,CurrencyType,
       case when CurrencyType = 1 then Total else 0 AS TotalOnLocalCurrency,
       case when CurrencyType = 2 then Total else 0 AS TotalOnOtherCurrency,
      (TotalOnLocalCurrency/dbo.GetCurrencyRate(SalesDate)) + TotalOnOtherCurrency) Total
FROM Sales

If this scenario possible, I mean I tried to do this but I got this error:

Invalid column name 'TotalOnLocalCurrency'.

Upvotes: 1

Views: 210

Answers (4)

Beth
Beth

Reputation: 9617

Try something more like:

select 
SalesID,SalesType,SalesDate,CurrencyType,
TotalOnLocalCurrency,TotalOnOtherCurrency,
TotalOnLocalCurrency/dbo.GetCurrencyRate(SalesDate)) + TotalOnOtherCurrency as total from
(
    SELECT SalesID,SalesType,SalesDate,CurrencyType,
           case when CurrencyType = 1 then Total else 0 END AS TotalOnLocalCurrency,
           case when CurrencyType = 2 then Total else 0 END AS TotalOnOtherCurrency
    FROM Sales) t

Upvotes: 0

marc_s
marc_s

Reputation: 755217

You cannot use column aliases that you introduce within the same query. If you're on SQL Server 2005 and newer, I would use a CTE (Common Table Expression) to handle this.

;WITH SalesData AS
(
    SELECT 
        SalesID, SalesType, SalesDate, CurrencyType,
        TotalOnLocalCurrency = CASE WHEN CurrencyType = 1 THEN Total ELSE 0 END,
        TotalOnOtherCurrency = CASE WHEN CurrencyType = 2 THEN Total ELSE END 
    FROM Sales
)
SELECT 
   SalesID, SalesType, SalesDate, CurrencyType,
   TotalOnLocalCurrency, TotalOnOtherCurrency,
   Total = (TotalOnLocalCurrency/dbo.GetCurrencyRate(SalesDate)) + TotalOnOtherCurrency) 
FROM  
   SalesData

Upvotes: 0

J Cooper
J Cooper

Reputation: 5008

Your are getting the error because you have aliased the expression CurrencyType = 1 then Total else 0 AS TotalOnLocalCurrency. You cannot refer to alias column names in the same select clause, this violates logical query processing rules. Instead, either refer to the expressions themselves or wrap the expressions in a table expression and query against the table expression.

Upvotes: 1

JNK
JNK

Reputation: 65197

You can either put the whole formula in the subsequent lines, or use CROSS APPPLY for this:

SELECT SalesID,
       SalesType,
       SalesDate,
       CurrencyType,
       TotalOnLocalCurrency,
       TotalOnOtherCurrency,
      (TotalOnLocalCurrency/dbo.GetCurrencyRate(SalesDate)) + TotalOnOtherCurrency Total
FROM Sales
CROSS APPLY (SELECT CASE WHEN CurrencyType = 1 then Total else 0) CxA(TotalOnLocalCurrency)
CROSS APPLY (SELECT case when CurrencyType = 2 then Total else 0) CxB(TotalOnOtherCurrency)

Upvotes: 2

Related Questions