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));
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));
10:17:07 Error Code: 1054. Unknown column 'be base' in 'field list' 0.062 sec
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
And thirdly, in the subselect it is mandatory to wrap the aliased column with ` (backticks), simple quotes won't work there.