1

I am in the process to move a really small database from Access to MySQL and I have some queries to translate.

I have this really simple query (in Access db):

SELECT InvoiceNumber,
       Field1 + Field2 AS CalcField1,
       Field3 + Field4 AS CalcField2,
       (CalcField1 - CalcField2) / CalcField2 AS Percentage ;

This does not work in MySQL, because it does not allow column aliases in formulas. I learnt here that the solution is to wrap the alias with a (SELECT alias). That works, but the result is really ugly and becomes unreadable. In my case it is:

((SELECT ((SELECT CalcField1) - (SELECT CalcField2))) / (SELECT CalcField2)) AS Percentage

In the former case I can understand immediately the expression, in the latter I cannot.

My question is: is there an alternative way to use aliases in MySQL formulas that have a better readability?

1
  • I have to use the resulting SQL code it in a view.
    – Giorgio R
    Aug 4, 2015 at 10:05

1 Answer 1

1

It's more readable if you use a derived table. No need for 4 SELECT clauses, just 2. Plus, it's proper SQL, not some fancy feature that MySQL or Access has added:

SELECT InvoiceNumber,
       CalcField1,
       CalcField2,
       (CalcField1 - CalcField2) / CalcField2 AS Percentage 
FROM 
    ( SELECT InvoiceNumber,
             Field1 + Field2 AS CalcField1,
             Field3 + Field4 AS CalcField2
      FROM ...
    ) AS x ;

The other option, is not to use a derived table at all but double the calculated expressions:

SELECT InvoiceNumber,
       Field1 + Field2 AS CalcField1,
       Field3 + Field4 AS CalcField2,
       ((Field1 + Field2) - (Field3 + Field4)) / (Field3 + Field4) 
         AS Percentage 
FROM ... ; 
3
  • The first option works if I use it like a query, if I try to create a view with the very same query, I get an error: Error 1349: View's SELECT contains a subquery in the FROM clause SQL Statement
    – Giorgio R
    Aug 4, 2015 at 9:19
  • Yes, you can't have a view with a derived table. That's a known limitation of mysql. But that was not in your question (that you want a view). Aug 4, 2015 at 9:29
  • Yes, it was not in my question: I am a newbie with MySQL and I took for granted that whatever worked as a query It would have worked as a view.
    – Giorgio R
    Aug 4, 2015 at 10:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.