Using an SQL Alias in a calculation to create additional Alias

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

András Ottó · Accepted Answer · 2012-09-07 15:22:43Z

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

answered Sep 7, 2012 at 15:22

András Ottó

7,7351 gold badge30 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joe Stefanelli · Accepted Answer · 2012-09-07 15:24:24Z

0

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
...

answered Sep 7, 2012 at 15:24

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

Comments

Guffa · Accepted Answer · 2012-09-07 15:26:22Z

0

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

answered Sep 7, 2012 at 15:26

Guffa

703k111 gold badges760 silver badges1k bronze badges

Comments

Freddy Jose Chirinos Moròn · Accepted Answer · 2016-08-30 20:16:59Z

0

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

answered Aug 30, 2016 at 20:16

Freddy Jose Chirinos Moròn

3462 silver badges9 bronze badges

Collectives™ on Stack Overflow

Using an SQL Alias in a calculation to create additional Alias

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related