Using a calculate column in the Where clause

• bob mazzo

  SSCommitted

  Points: 1684

  More actions

  September 5, 2007 at 2:44 pm

  #180936

  How can I use a calculated column in the Where clause of that same Select statement.

  Example:

  Select a.order_number, a.customer, qty_in_stock = stock_calc(<param&gt

  From Orders a

  Where qty_in_stock >10

  This is NOT my actual query but this is the idea I want to implement. Of course I get the error on "qty_in_stock" - "Invalid column name qty_in_stock".

  Is there a work-around on that ?

  Thank you in advance,

  Bob

   

• Mike Howell

  Mr or Mrs. 500

  Points: 521

  More actions

  September 5, 2007 at 4:21 pm

  #731638

  Try putting your calculation in the where clause, not the aliased column name

  Select a.order_number, a.customer, qty_in_stock = stock_calc(<param>

  From Orders a

  Where stock_calc(<param&gt > 10

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  September 6, 2007 at 4:42 am

  #731752

  Or use a subquery

  Select a.order_number, a.customer, qty_in_stock

  From (

  SELECT order_number, customer, stock_calc(<param> ) AS qty_in_stock 

  FROM Orders) a

  Where qty_in_stock  > 10

  Aliases haven't been processed when the where clause executes, so they cause errors

   

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Tomm Carr

  SSCertifiable

  Points: 5426

  More actions

  September 7, 2007 at 1:22 pm

  #732254

  Repeat the calculation in the WHERE clause. Yeah, it looks ugly. However, if looks are important to you (and I have to admit, sometimes I get more than a little stuck on looks myself) then you can hide it by filtering through a derived table:

  Select *
  From (
      select  order_number, customer, stock_calc( <param> ) as qty_in_stock
      from orders
  ) as a
  Where qty_in_stock > 10

  I don't usually bother with such simple calculations. Where this comes in handy is when you have a query like:

  select something, otherthing, case when ...
  from sometable
  order by case when...

  Tomm Carr
  --
  Version Normal Form -- http://groups.google.com/group/vrdbms