Blow ThemUp Blow ThemUp - 3 months ago 8
SQL Question

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

Answer

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