Geek asks and answers

Reusing an alias in a computed field

Good afternoon, I wonder if anyone can point me in the right direction, as I'm struggling a bit. I have a mysql query that should include an alias in a computed 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 the "Unknown Paid Columns in the Field List" error, is there a way to use the Paid alias after it is created at all? I am trying to start tp a new system created in Access and SQL, they just used saved / SP queries for this.

+3
source share
3 answers

Is not allowed. You cannot use a column as an alias when the alias and another column are at the same level SELECT.

, - -

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

mysql :

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
+3

(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
-1

More articles:


All Articles