quote:

---

Originally posted by DonAtWork

You cannot use an aggregate in the where clause. You will have to use the HAVING clause.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

---

quote:

---

Originally posted by OWSQL

Hi
Is it possible to include a WHERE clause in the select part of a query?
For instance below I would to have a clause on the field "sum(valuationdetail.exposurequantity) as Exposure," so I can decide to see only field different from Zero or below 50,000.

Thanks


SELECT
'Option Valuation' as AllegroReport,
valuationdetail.tradebook tradebook,
valuationdetail.tradetype tradetype,
product.producttype producttype,
valuationdetail.product product,
valuationdetail.pricestatus pricestatus,
valuationdetail.unit unit,
valuationdetail.priceindex priceindex,
valuationdetail.begtime begtime,
DATEADD(MONTH, DATEDIFF(MONTH, '19000101', valuationdetail.begtime), '19000101') as DeliveryMonth,
sum(valuationdetail.exposurequantity) as Exposure,
valuationdetail.strikeprice strikeprice,
position.optionstyle optionstyle,
position.optiontype optiontype,
valuationdetail.optrefprice optrefprice,
sum(valuationdetail.delta * valuationdetail.exposurequantity) as deltaqty,
sum(valuationdetail.gamma * valuationdetail.exposurequantity)as gammaqty,
sum(valuationdetail.theta * valuationdetail.exposurequantity) as thetavalue,
sum(valuationdetail.vega * valuationdetail.exposurequantity) as vegavalue
FROM valuation, valuationdetail, position, finposition, product
WHERE valuation.valuationmode = 'Position'
and valuation.valuation=valuationdetail.valuation
and valuationdetail.position=finposition.position
and valuationdetail.posdetail=finposition.posdetail
and valuationdetail.position = position.position
and valuationdetail.product = product.product
and valuationdetail.quantitytype <> 'LOSS'
and ( (valuation.valuationtype = 'SUMMATION' and valuation.valuationtime = @ValuationT)
or (valuation.valuationtype = 'INCREMENTAL' and valuation.valuationtime >= @ValuationT
and valuation.valuationtime <= @ValuationT) )
and position.trade is not null
AND ((valuationdetail.begtime< @EndT OR valuationdetail.begtime is null)
AND (valuationdetail.endtime> @BegT OR valuationdetail.endtime is null))
AND ( ( (valuationdetail.feetype <> 'Clearing Fees' OR valuationdetail.feetype IS NULL)
and valuationdetail.feetype IS NULL and ( valuationdetail.tradetype = 'Option Exchange'
or valuationdetail.tradetype = 'Futures' or valuationdetail.tradetype = 'Option OTC' )
and position.tradebook = 'IRM Hedging' ) AND valuationdetail.tradetype IN ('Option Exchange','Option OTC') )
and exposurequantity <> '0'
group by valuationdetail.tradebook, valuationdetail.tradetype, product.producttype,valuationdetail.product,valuationdetail.pricestatus,valuationdetail.unit,valuationdetail.priceindex,valuationdetail.begtime, valuationdetail.strikeprice, position.optionstyle,position.optiontype, valuationdetail.optrefprice
order by AllegroReport,valuationdetail.tradebook, valuationdetail.tradetype, product.producttype,valuationdetail.product,valuationdetail.pricestatus,valuationdetail.unit,valuationdetail.priceindex,valuationdetail.begtime, valuationdetail.strikeprice, position.optionstyle,position.optiontype, valuationdetail.optrefprice
having sum(valuationdetail.exposurequantity) > 0

---