/build/static/layout/Breadcrumb_cap_w.png

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!


0 Comments   [ + ] Show comments

Answers (2)

Answer Summary:
Posted by: jverbosk 11 years ago
Red Belt
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


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 11 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 11 years ago
  • AHhhhhhh I see what I was doing... Thanks! - jobla 11 years ago
Posted by: chucksteel 11 years ago
Red Belt
0

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

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ