Use select element for condition problem in SQL server

Question

I want to write a sql like below pseudo code:

[CODE]

SEELCT A.CURR_CD AS [CURRENCY CODE],

(CASE [CURRENCY CODE] = 'US' THEN 1

ELSE [CURRENCY CODE] = 'UK' THEN 2

ELSE 3 END) * B.AMOUNT AS [ACTUAL AMOUNT]

FROM CURRENCY WHERE [B][I][ACTUAL AMOUNT][/I][/B] > 20;

[/CODE]

Can I use the alias name in where condition from select element ? Except copy the select element [Actual amount], how could I write?

Answer 1

Could anyone help!! Thx!!

Answer 2

Hi Ringo,

No, it is possible. It is because the where clause evaluation is prior to the select clause, so the alias can't be called in where clause. You can use common expression table (CTE) to wrap your code and then to call the alias. See http://msdn.microsoft.com/en-us/library/ms175972.aspx to learn more information to use CTE.

So you code can be something like below:

With CTE1([CURRENCY CODE],[ACTUAL AMOUNT]) AS

(
---put your sql here---
)

SELECT * FROM CTE1 WHERE [ACTUAL AMONT] > 20

regards,
Jerry