title: Using an SQL Alias in a calculation to create additional Alias
body: <p>I have the following SQl statement:</p>
  
  <pre><code>SELECT id  + 100000000000000000 AS id,  
      a.external_code + CAST(id AS varchar(15)) as crUniqueId,  
      m.check_amount,  
      'C' as crType,  
      m.postprint_date as activationDate,  
      m.postprint_date as creationDate,  
      m.memo_explanation as reasonLine1,  
      m.check_no, 
      m.check_amount - sh.sumAmount As dispositionAmount 
      FROM md_master m 
      Join accounts a on m.account_tag = a.acct_id 
      LEFT JOIN (SELECT master_id,  
                  SUM(md_cr_pending.current_amount) as sumAmount  
                  FROM md_cr_pending 
                  Group BY master_id) sh ON master_id = m.ID 
      WHERE (m.postprint_tag = 2) OR (m.postprint_tag = 4) OR  
            (m.postprint_tag = 5) OR (m.postprint_tag = 7) 
  
      UNION ALL  
  
      SELECT id + 200000000000000000 as id,  
      'PERCHK' + CAST(id AS varchar(15)) as crUniqueId,  
      check_amount,  
      'P' as crType,  
      business_date as activationDate,  
      business_date as creationDate,  
      identify_description as reasonLine1,  
      check_no, 
      check_amount - sh.sumAmount As dispositionAmount 
      FROM cd_personal_checks  
      LEFT JOIN (SELECT cd_personal_checks_id,  
                  SUM(md_cr_pending.current_amount) as sumAmount  
                  FROM md_cr_pending 
                  Group BY  cd_personal_checks_id) sh  
                  ON sh.cd_personal_checks_id = cd_personal_checks.ID
  </code></pre>
  
  <p>I would like to add an additional column to each of the select statements of the UNION. However, I need to use a alias created as the previous column.</p>
  
  <p>For example. I would like to do the following</p>
  
  <pre><code>m.check_amount - sh.sumAmount As dispositionAmount, m.check_amount-dispositionAmount AS openBalance
  </code></pre>
  
  <p>and</p>
  
  <pre><code>check_amount - sh.sumAmount As dispositionAmount,  check_amount-dispositionAmount AS openBalance
  </code></pre>
  
  <p>respectively</p>
  
  <p>Can this be done? Currently, the way i am doing it above, I get a "Invalid column name 'dispositionAmount'"</p>
  
  <p>thanx</p>