How do I use alias in where clause? [duplicate]

Possible Duplicate:
Referring to a Column Alias in a WHERE Clause

 SELECT
Trade.TradeId,
Isnull(Securities.SecurityType,'Other') SecurityType,
TableName,
CASE
WHEN
SecurityTrade.SecurityId IS NOT NULL
THEN
SecurityTrade.SecurityId
ELSE
Trade.SecurityId
END AS PricingSecurityID,
sum(Trade.Quantity)OVER(Partition by Securities.SecurityType, SecurityTrade.SecurityId,Trade.Price, Buy,Long ) as sumQuantity,
--added porfolio id for Getsumofqantity
Trade.PortfolioId,
Trade.Price,
case
when (Buy = 1 and Long = 1) then 1
when (Buy = 0 and Long = 0) then 1
else 0
end Position
from
Fireball_Reporting..Trade
where porfolioid =5 and Position =1 

i want to use Position =1 in my where clause which is an alias of case

case
when (Buy = 1 and Long = 1) then 1
when (Buy = 0 and Long = 0) then 1
else 0
end Position

How can I use it in where clause?

I tried zo directly use that CASE statement in where clause, but failed.

WHERE Trade.SecurityId = @SecurityId AND PortfolioId = @GHPortfolioID AND (case when (Buy = 1 and Long = 1) then 1 when (Buy = 0 and Long = 0) then 1 else 0 end Position = 1)

3

4 Answers

The SQL-Server docs says:

column_alias can be used in an ORDER BY clause, but it cannot be used in a WHERE, GROUP BY, or HAVING clause.

Similar in the MySQL doc it says:

Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined.

In MySQL you can at least reuse aliases in the SELECT clause

0

You can't, not directly.

If you wrap the whole query in a sub-query, however, it works fine.

SELECT *
FROM
( SELECT Trade.TradeId, Isnull(Securities.SecurityType,'Other') SecurityType, TableName, CASE WHEN SecurityTrade.SecurityId IS NOT NULL THEN SecurityTrade.SecurityId ELSE Trade.SecurityId END AS PricingSecurityID, sum(Trade.Quantity)OVER(Partition by Securities.SecurityType, SecurityTrade.SecurityId,Trade.Price, Buy,Long ) as sumQuantity, --added porfolio id for Getsumofqantity Trade.PortfolioId, Trade.Price, case when (Buy = 1 and Long = 1) then 1 when (Buy = 0 and Long = 0) then 1 else 0 end Position from Fireball_Reporting..Trade where porfolioid = 5
) AS data
WHERE Position = 1 

This means that you don't need to repeat the CASE statement in WHERE clause. (Maintainable and DRY).

It is also a structure that allows the optimiser to behave as if you had simply repeated yourself in the WHERE clause.

It's also very portable to other RDBMSs.

In SQL Server, then you also have another option...

SELECT Trade.TradeId, Isnull(Securities.SecurityType,'Other') SecurityType, TableName, CASE WHEN SecurityTrade.SecurityId IS NOT NULL THEN SecurityTrade.SecurityId ELSE Trade.SecurityId END AS PricingSecurityID, sum(Trade.Quantity)OVER(Partition by Securities.SecurityType, SecurityTrade.SecurityId,Trade.Price, Buy,Long ) as sumQuantity, --added porfolio id for Getsumofqantity Trade.PortfolioId, Trade.Price, position.val AS Position
from Fireball_Reporting..Trade
CROSS APPLY
( SELECT case when (Buy = 1 and Long = 1) then 1 when (Buy = 0 and Long = 0) then 1 else 0 end AS val
) AS position
where porfolioid = 5 AND position.val = 1

2

You can't directly do this...but you can wrap an additional select around it all and use the where clause:

 select * from ( SELECT Trade.TradeId, Isnull(Securities.SecurityType,'Other') SecurityType, TableName, CASE WHEN SecurityTrade.SecurityId IS NOT NULL THEN SecurityTrade.SecurityId ELSE Trade.SecurityId END AS PricingSecurityID, sum(Trade.Quantity)OVER(Partition by Securities.SecurityType, SecurityTrade.SecurityId,Trade.Price, Buy,Long ) as sumQuantity, --added porfolio id for Getsumofqantity Trade.PortfolioId, Trade.Price, case when (Buy = 1 and Long = 1) then 1 when (Buy = 0 and Long = 0) then 1 else 0 end Position from Fireball_Reporting..Trade where porfolioid =5 and Position =1 )x where x.position = 1

I'm probably missing something but surely this will cover it:

WHERE (Buy = 1 and Long = 1) OR (Buy = 0 and Long = 0)

1