t-pinto (9/16/2008)
Hello,I have an alias for 2 calculations. I would like to filter only those results that are >1000.
Here is my statement.
Select InvoiceTotal,
InvoiceTotal*(.10) as "10%",
InvoiceTotal+(InvoiceTotal*(.10)) as "Plus 10%"
From Invoices
Where "10%" and "Plus 10%" >1000
You have three options:
1) Use the calculation in the where clause:
Select InvoiceTotal
,InvoiceTotal*(.10) as "10%"
,InvoiceTotal+(InvoiceTotal*(.10)) as "Plus 10%"
From Invoices
Where InvoiceTotal*(.10) > 1000
And InvoiceTotal+(InvoiceTotal*(.10)) > 1000;
2) Use a derived table:
Select InvoiceTotal
,[10%]
,[Plus 10%]
From (Select InvoiceTotal
,InvoiceTotal*(.10) as "10%"
,InvoiceTotal+(InvoiceTotal*(.10)) as "Plus 10%"
From Invoices) t
Where t.[10%] > 1000
And t.[Plus 10%] > 1000;
3) Use a common table expression (CTE):
With cte (InvoiceTotal, 10Percent, Plus10Percent)
As (Select InvoiceTotal
,InvoiceTotal*(.10) as "10%"
,InvoiceTotal+(InvoiceTotal*(.10)) as "Plus 10%"
From Invoices)
Select *
From cte
Where 10Percent > 1000
And Plus10Percent > 1000;
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs