Reuse Alias on calculated field

Question

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

The MS-Access alias feature is non-standard and will not work/compile for any other DBMS platform. You can create use-defined functions to accomplish the same things. — ron tornambe, May 22 '12 at 15:47
You can't use the alias in the select statement on Mysql, you'll have to recalculate it — Nico, May 22 '12 at 15:54
@Juniad, I have edited original code to reflect actual sql query, sorry I should have [posted full code originally. — gary, May 23 '12 at 08:01

score 3 · Accepted Answer · answered May 23 '12 at 08:17

3

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);

answered May 23 '12 at 08:17

JHS

7,761
2
29
53

Ok thanks for your input, I managed to sort this by using mysql view, works a treat.. – gary May 23 '12 at 10:36

score 3 · Answer 2 · answered Oct 29 '15 at 12:09

You can use variables in mysql for this stuff:

Select tblComms._CommMonth, 
    tblComms._Reference, 
    tblComms._ClientName, 
    tblComms._Premium, 
    tblComms._CommDue, 
    tblComms._Employee_Name, 
    tblCont.Retention, 
    @Paid := (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

score -1 · Answer 3 · answered Oct 26 '16 at 01:31

use (select Paid)

 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) - (select 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

Reuse Alias on calculated field

3 Answers3

Related