A Krolak A Krolak - 2 years ago 68
SQL Question

SQL Query Differences between MS Access and MySQL

I'm having trouble converting a MS Access Query into a MySQL view. It seems that in Access, you're able to use a field that's created within the same select statement later on in the select statement. Whereas, in MySQL, that seems to not be the case.

e.g. Access Generated Code

SELECT IIf([Bonus]>0,[Base Salary],0) AS [BE Base], [BE Base]+[Bonus] AS [BE TC]
FROM titles_tbl INNER JOIN wage_tbl ON titles_tbl.Job_Title = wage_tbl.[Standard Title]
WHERE (((titles_tbl.Mod_Code)="3B") AND ((wage_tbl.Bonus)>0));

vs MySQL Converted Code

select if(`bonus`>0,`base salary`,0) as `be base`, `be base`+`bonus` as `be tc`
from titles_tbl inner join wage_tbl on titles_tbl.job_title = wage_tbl.`standard title`
where (((titles_tbl.mod_code)="3b") and ((wage_tbl.bonus)>0));

When I put this Access-generated SQL code into MySQL (after converting it appropriately), I get an error saying

10:17:07 Error Code: 1054. Unknown column 'be base' in 'field list' 0.062 sec

To me, it seems like MySQL can't use a new column in the same select statement that it's generated in.

Does anyone know whether that's the case, or what the real problem might be (as well as a solution)? Thank you in advance!

Answer Source

Your MySQL code should be

select if(`bonus`>0,`base salary`,0) as `be base`,  
   ((SELECT `be base`) + `bonus`) as `be tc`
from ...

Firstly, if you use spaces in aliases you should surround the alias with ' (quote) or ` (backticks)

Secondly, if you want to refer, in a SELECT, to an alias of the same SELECT, you have to (SELECT `the_alias`)

And thirdly, in the subselect it is mandatory to wrap the aliased column with ` (backticks), simple quotes won't work there.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download