Using an SQL Alias in a calculation to create addi

Question

I have the following SQl statement:

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

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.

For example. I would like to do the following

m.check_amount - sh.sumAmount As dispositionAmount, m.check_amount-dispositionAmount AS openBalance

and

check_amount - sh.sumAmount As dispositionAmount,  check_amount-dispositionAmount AS openBalance

respectively

Can this be done? Currently, the way i am doing it above, I get a "Invalid column name 'dispositionAmount'"

thanx

Answer 1

The expression check_amount-dispositionAmount is the same as check_amount-(check_amount - sh.sumAmount), which is the same as (check_amount-check_amount) + sh.sumAmount, so that is just sh.sumAmount.

So:

check_amount - sh.sumAmount As dispositionAmount, sh.sumAmount AS openBalance

Answer 2

No it can't be on the same level, since the alias what you give in your select only "available" in the Order By part. This is because the logical query processing.

For this you should build more level, or use the expression again.

And I don't know, that you want to do this:

m.check_amount - sh.sumAmount As dispositionAmount, 
m.check_amount - dispositionAmount AS openBalance

Because it is equal then:

m.check_amount - sh.sumAmount As dispositionAmount, 
m.check_amount - m.check_amount + sh.sumAmountAS AS openBalance

Which is:

m.check_amount - sh.sumAmount As dispositionAmount, 
sh.sumAmountAS AS openBalance

Answer 3

You can use the CROSS APPLY CROSS APPLY

SELECT my_num, my_num*5 AS another_number FROM table CROSS APPLY (SELECT 5 AS my_num) X

Answer 4

No, it can't be done. You'll need to repeat the calcuation of dispositonAmount.

...
m.check_amount - sh.sumAmount As dispositionAmount, 
m.check_amount - (m.check_amount - sh.sumAmount) AS openBalance
...