SQL Primer: Having difficulty running the sql query I wrote reading the sql primer as a report.
I'm having difficulty running the report that I wrote after reading the SQL primer. My report is supposed to give me all the machines that are a part of the label 'Windows XP Machines' but when I run the report in any format I get an error that says:
Exception while running report. Unknown column name : SYSTEM_NAME
When I use the Report 'Preview' feature I don't get this error, I only get it when I try and run the report.
Here is my SQL code:
SELECT DISTINCT M.NAME AS NAME, L.NAME AS LABEL FROM MACHINE M JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID) JOIN LABEL L ON (ML.LABEL_ID = L.ID) WHERE (L.NAME rlike 'XP Pro systems') ORDER BY NAME
Thanks for your help!
Community Chosen Answer
This is due to your alias of L.NAME AS LABEL - if you use a different alias (i.e. LABEL_NAME, 'Label Name', etc) it works fine.
The error is related to the use of a special MySQL command as an alias (certain words you can't use, like LABEL, SELECT, FROM, etc). This came up recently in another question and is something the MySQL Query Browser will help you identify, as the special words will turn *blue* (by default) to indicate they are MySQL commands (functions, etc) and can't be used as aliases.
One last thing - try to avoid the use of DISTINCT unless there's no other way to remove duplicates. If you have multiple machines with the same name (?!), then you basically have no choice. But if you are getting multiple matches due to the WHERE statement, then try to refine it to avoid the duplicates. This is a better way to go in the long run, as DISTINCT can sometimes hide issues with queries (i.e. unintended results that you should be aware of).
Hope that helps!