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

Question

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) 

Answer 1

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

Answer 2

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