SELECT ExchangeRatePrice = CASE pp.Price
WHEN NULL THEN 0
ELSE (CASE WHEN c.CurrencyId = 1 THEN pp.Price
ELSE CONVERT(DECIMAL(9, 2), (pp.Price * c.ExchangeRate)) END)
END ,
price as OriginalPriceInDB,
10 * Price as CalculatedPrice,
c.currencyid as Currency
FROM ProductPrice pp, currency c
alt text http://img682.imageshack.us/img682/3692/exchangerate.png
I want calculated column (ExchangeRatePrice) to use in CalculatedPrice. Can I use it straight of I need to convert it again?
I have used 10 * Price just to show you the example because if I use 'ExchangeRatePrice' here it will give error '开发者_运维知识库Invalid Column name'
To reference pre-calculate a value in SQL usually means performing the calculation in an inner query (AKA inline view):
SELECT x.exchangerateprice,
x.OriginalPriceInDB,
10 * x.exchangerateprice AS CalculatedPrice,
x.currencyid
FROM (SELECT CASE
WHEN pp.price IS NULL THEN
0
WHEN c.CurrencyId = 1 THEN
pp.Price
ELSE
CONVERT(DECIMAL(9, 2), (pp.Price * c.ExchangeRate))
END AS ExchangeRatePrice,
price as OriginalPriceInDB,
c.currencyid as Currency
FROM PRODUCTPRICE pp,
CURRENCY c) x
This is the equivalent to using the WITH
clause (available in SQL Server 2005+) - there's no difference in performance.
You could duplicate the logic:
SELECT CASE
WHEN pp.price IS NULL THEN
0
WHEN c.CurrencyId = 1 THEN
pp.Price
ELSE
CONVERT(DECIMAL(9, 2), (pp.Price * c.ExchangeRate))
END AS ExchangeRatePrice,
price as OriginalPriceInDB,
CASE
WHEN pp.price IS NULL THEN
0
WHEN c.CurrencyId = 1 THEN
pp.Price
ELSE
CONVERT(DECIMAL(9, 2), (pp.Price * c.ExchangeRate))
END * 10 AS CalculatedPrice,
c.currencyid as Currency
FROM PRODUCTPRICE pp,
CURRENCY c
..but that means duplication & inherent risk of someone not updating both instances to keep in sync.
Yes. Use "WITH" (Common Table Expressions)
With MainT as (
SELECT CASE pp.Price
WHEN NULL
THEN 0
ELSE (
CASE WHEN c.CurrencyId = 1 THEN pp.Price
ELSE CONVERT(DECIMAL(9, 2), (pp.Price * c.ExchangeRate)) END
)
END ExchangeRatePrice,
price as OriginalPriceInDB,
c.currencyid as Currency
FROM ProductPrice pp, currency c
)
select *, 10*ExchangeRatePrice as CalculatedPrice
from MainT
You can't use column aliases until the ORDER BY
clause.
But you can use column aliases defined in a subquery.
SELECT x * 10 AS y
FROM (SELECT 123 AS x) tbl
精彩评论