How do I use alias in where clause?
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
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
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