SQL Primer: Having difficulty running the sql query I wrote reading the sql primer as a report.
Hey,
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!
Answers (2)
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!
John
Comments:
-
Hmm I'm still having some difficulty:
SELECT MACHINE.NAME, LABEL.NAME
FROM ORG1
WHERE (LABEL.NAME rlike 'XP Pro systems')
Here's my thought process:
I want my query to produce a table with two columns, one that is the machine name and one that is the label name. I only want a computer to appear in my table if it has the 'XP Pro' label. If it does not have the XP pro label I don't want it to appear in my table... My query isn't working though, SQL is now giving me an error that says "ORG1.ORG1 doesn't exist' but what i want it to do is look in org 1 for the machine table and give me the name column, then I want it to look in the Label table and give me the name column, then I want it to only display the computers with a label name that contains "XP Pro" - jobla 12 years ago -
Change the FROM ORG1 statement to FROM MACHINE and it should work fine. You need to specify the table (in this case MACHINE) and only really need to specify the ORG if you have multiple. For example (and note that I changed the L.NAME target to match one of my own labels):
SELECT M.NAME AS NAME, L.NAME AS LABEL_NAME
FROM ORG1.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 'computers')
ORDER BY NAME
Also, be sure you actually have a label named "XP Pro systems" if you are going to filter with it.
John - jverbosk 12 years ago -
AHhhhhhh I see what I was doing... Thanks! - jobla 12 years ago
