CODEWITHSUNDEEP
sql-server
Eric Anderson
Eric Anderson

Reputation: 366

SQL Server : column alias accessing

I have a query that looks like this:

Select (numberLateOrders / numberOfOrders) as percentFailed
From Orders

Now I wanted to be able to add a Where clause and access the column alias percentFailed however it seems like the alias is not being recognized until the query completes. Is this true and/or is there a work around for this?

thanks!

Upvotes: 0

Views: 67

Answers (3)

Rahul
Rahul

Reputation: 77866

Yes it's true, the column alias won't be accessible/available in WHERE clause because of the logical order of execution in SQL and per that SELECT column alias comes after WHERE and so WHERE won't be able to recognize them. You will have to add the computation/expression in WHERE again like below

Select (numberLateOrders / numberOfOrders) as percentFailed
From Orders
WHERE (numberLateOrders / numberOfOrders) > some_value

Another solution (not better one) is to use a outer query like

select percentFailed
from 
(
    Select (numberLateOrders / numberOfOrders) as percentFailed
    From Orders 
) tab
where percentFailed > some_value

Upvotes: 3

mxix
mxix

Reputation: 3659

Just to add to the existing answers

Or do it with a Subquery

select
  percentFailed
from (
  Select (numberLateOrders / numberOfOrders) as percentFailed
  From Orders
) t
where
  percentFailed > 0

Upvotes: 2

Mihai
Mihai

Reputation: 26784

WHERE can`t see aliases,just repeat the expression

Select (numberLateOrders / numberOfOrders) as percentFailed
From Orders
WHERE (numberLateOrders / numberOfOrders) >0

Upvotes: 1

Related Questions