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!

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

3

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

Answered 10/15/2012 by: jverbosk
Red Belt

  • 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"
  • 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
  • AHhhhhhh I see what I was doing... Thanks!
Please log in to comment

Answers

0

Try removing the aliases from your SQL to see if that helps. 

Answered 10/15/2012 by: chucksteel
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity

Share