title: How do I use a SELECT ... AS in a WHERE clause
body: <p>Here is a piece of SQL code that does not work:</p>
  
  <pre><code>SELECT bl.regn_id,
     RTRIM(LTRIM(dv.dv_id))   + '_' + RTRIM(LTRIM(bl.regn_id)) AS bu_regn,
     (SELECT COUNT (em.em_id) 
        FROM em 
          LEFT OUTER JOIN bl bl_s ON em.bl_id = bl_s.bl_id
          LEFT OUTER JOIN irs_self_cert_em  ON  em.em_id = irs_self_cert_em.em_id
        WHERE dv.dv_id = em.dv_id
        AND bl.bl_id = bl_s.bl_id
        AND irs_self_cert_em.date_cert_loc &gt;= DATEADD(month, -1, GETDATE())
        AND (em.date_last_update_cads &gt;= (select date_last_update_completed FROM ddi_completed WHERE ddi_id='TRA_CADS_EM'))          
      ) AS certified 
  FROM bl       
      CROSS JOIN dv 
      WHERE bl.status = 'A'  AND (certified &gt; 0 )
  </code></pre>
  
  <p>I receive the error: "Lookup Error - SQL Server Database Error: Invalid column name 'certified'."</p>
  
  <p>As you can see I use a subquery within the SELECT statement and give it the name 'certified'. I then try to use that value in the WHERE clause.</p>
  
  <p>Can someone suggest and alternative way of accomplishing this?</p>
  
  <p>Many thanks,
  Matt</p>