gary gary - 6 months ago 41
SQL Question

Reuse Alias on calculated field

Good afternoon, wonder if anyone could point me in the right direction as I am struggling a little. I have a mysql query that I need to include an alias in a calculated field as such:

Select tblComms._CommMonth,
tblComms._Reference,
tblComms._ClientName,
tblComms._Premium,
tblComms._CommDue,
tblComms._Employee_Name,
tblCont.Retention,
(tblComms._CommDue) * (tblCont.Retention) / 100 As Paid,
(tblComms._CommDue) - (Paid) As Payable
From tblComms Inner Join dbo_companyref On
dbo_companyref._Reference = tblComms._Reference
Inner Join tblCont
On dbo_companyref._Advisor_Name = tblCont._Employee_Name


This returns an error "Unknown columns 'Paid' in field list", is there any way I can use the Paid alias after its been created at all? I am trying tp roll out a new system which was created in Access & SQL, they simply used saved queries / SPs for that..

JHS JHS
Answer

Its not allowed. You cannot use the column as an alias when the alias and other column are in the same level of SELECT.

You could have used the alias if it was something like this -

SELECT alias
FROM (SELECT column1 AS alias
      FROM table);