CODEWITHSUNDEEP
mysqlsubquery
gary
gary

Reputation: 319

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

Upvotes: 4

Views: 5772

Answers (3)

jargalan
jargalan

Reputation: 5194

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

Upvotes: -1

Nabeel
Nabeel

Reputation: 1001

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

Upvotes: 3

JHS
JHS

Reputation: 7871

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

Upvotes: 3

Related Questions