sql query with alias name

Question

i have a table with this columns--- Or

orgid ispaid validity      noofthingstoTake

1      yes    2010-06-05      20
2      yes    2010-06-09       7

i have used this query(to join two more tableS):

select distinct B.RequirementID,A.OrganizationID 
from 
Organization A,RequirementsDetailsforOrganization B,validityorgdet F
where A.OrganizationID=B.OrganizationID and F.orgid=A.OrganizationID and
 F.ispaid=1 and F.validity>=GETDATE() and 

  F.noofthingstoTake> ?? 

but i dont know how to check the (noofthingstaken) over here. it should not exceed 20. im passing this query from my code behind page to the Sql. how to get the query excute to check it should not exceed the noofthingstaken

pls help me out....????

Answer 1

Try this

select distinct B.RequirementID,A.OrganizationID from 
Organization A,RequirementsDetailsforOrganization B,validityorgdet F
where A.OrganizationID=B.OrganizationID and F.orgid=A.OrganizationID and
 F.ispaid=1 and F.validity>=GETDATE() and   F.noofthingstoTake <= 20

Answer 2

Presumably noofthingstoTake is actually an alias and not a column name in your table. You can't use column aliases outside of the select clause because they don't actually exist until the query is done running. So, you can't compare directly to noofthingstoTake, but must instead refer to the actual field name that that column came from. If it's an expression, just use the entire expression. Note that if it's an aggregate, you'll need to put it in a having clause, not a where clause.

(Note: You really should have posted your entire query)