Is it possible to use a calculated field in the WHERE clause of the same sentece?

• bottasergio

  SSC Rookie

  Points: 30

  More actions

  January 14, 2019 at 4:39 am

  #381182

  Is it possible to use a calculated field in the WHERE clause of the same sentence?
  
  I have a calculated field that is a value extracted from other table. I need to use this value in the WHERE clause of the main sentence.  Something like…
  
  SELECT field1, field2,
( SELECT TOP 1 otherfield FROM othertable WHERE somefield = t1.field1 ORDER BY something ) AS calculatedfield
FROM maintable t1
WHERE field1=500 AND calculatedfield >=2000
  Or the only way is to “copy” the calculated field sentence in the WHERE clause? …
  
  SELECT field1, field2,
( SELECT TOP 1 otherfield FROM othertable WHERE somefield = t1.field1 ORDER BY something ) AS calculatedfield
FROM maintable t1
WHERE field1=500
AND ( SELECT TOP 1 otherfield FROM othertable WHERE somefield = t1.field1 ORDER BY something ) >= 2000
  What about time processing... or resources…  I mean, the second way doesn´t  makes SQL calculate the calculated field twice?

• frederico_fonseca

  SSCoach

  Points: 16017

  More actions

  January 14, 2019 at 4:51 am

  #2018814

  either repeat or use one of the following 2 options.
  
select *
from (select t1.field1
    , t1.field2
    , (select top 1 otherfield
     from othertable ot
     where ot.somefield = t1.field1
     order by ot.something
     ) as calculatedfield
   from maintable t1
   where field1 = 500
) t
where calculatedfield >= 2000

  /*
or better yet
*/

  select t1.field1
  , t1.field2
  , calc.calculatedfield
from maintable t1
outer apply (select top 1 ot.otherfield as calculatedfield
     from othertable ot
     where ot.somefield = t1.field1
     order by ot.something
    ) as calc
where field1 = 500
and calculatedfield >= 2000