Using an SQL Alias in a calculation to create additional Alias

I have the following SQl statement:

SELECT id  + 100000000000000000 AS id,  
    a.external_code + CAST(id AS varchar(15)) as crUniqueId,  
    'C' as crType,  
    m.postprint_date as activationDate,  
    m.postprint_date as creationDate,  
    m.memo_explanation as reasonLine1,  
    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) 


    SELECT id + 200000000000000000 as id,  
    'PERCHK' + CAST(id AS varchar(15)) as crUniqueId,  
    'P' as crType,  
    business_date as activationDate,  
    business_date as creationDate,  
    identify_description as reasonLine1,  
    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


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


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



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

Need Your Help

Manage the memory of objects calling a delegate callback block

objective-c cocoa objective-c-blocks grand-central-dispatch

Say I have a class ObjectA (a view controller for example), that does this :

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.