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

Answers


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

Flex datagrid column disable and enable

flex actionscript flex3 flex-datagrid

In my flex application im using a datagrid with following columns

Eclipse environment variables

java environment-variables

I use Eclipse. I set for my project some environment variables. Can I get it from my java code with System.getProperty("SOME_VAR") ?

How do you determine if a variable contains a file pointer in PHP?

php

Exactly as the question states: How can you check if a variable in PHP contains a file pointer? Some like is_string() or is_object().

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.