Power Management Reports
Hi everyone,
I used a MySQL browser to generate the query I needed to collect my data and it worked fine in the browser but generates an error when I try to create a new SQL report with it.
Here is the SQL statement:
SELECT DISTINCT M.NAME, M.IP, L.NAME FROM MACHINE M, MACHINE_LABEL_JT J, LABEL L
WHERE M.ID=J.MACHINE_ID
AND L.ID=J.LABEL_ID
AND L.NAME LIKE "PGE_%"
ORDER BY L.NAME;
Here is the error it generates within the report:
Error Running Report
Exception while running report. net.sf.jasperreports.engine.JRException: Duplicate declaration of field : NAME
I'm trying to generate this list to show machine names and IP addresses that are associated with PG&E billing statement numbers (that were set up as smart labels). We need to generate this list to qualify for rebate money from PG&E.
Thanks!
-Alex
I used a MySQL browser to generate the query I needed to collect my data and it worked fine in the browser but generates an error when I try to create a new SQL report with it.
Here is the SQL statement:
SELECT DISTINCT M.NAME, M.IP, L.NAME FROM MACHINE M, MACHINE_LABEL_JT J, LABEL L
WHERE M.ID=J.MACHINE_ID
AND L.ID=J.LABEL_ID
AND L.NAME LIKE "PGE_%"
ORDER BY L.NAME;
Here is the error it generates within the report:
Error Running Report
Exception while running report. net.sf.jasperreports.engine.JRException: Duplicate declaration of field : NAME
I'm trying to generate this list to show machine names and IP addresses that are associated with PG&E billing statement numbers (that were set up as smart labels). We need to generate this list to qualify for rebate money from PG&E.
Thanks!
-Alex
0 Comments
[ + ] Show comments
Answers (7)
Please log in to answer
Posted by:
GillySpy
13 years ago
How about this to alias (rename) those columns that are confusing the reporting engine
SELECT DISTINCT M.NAME MACHINE_NAME, M.IP, L.NAME LABEL_NAME FROM MACHINE M, MACHINE_LABEL_JT J, LABEL L
WHERE M.ID=J.MACHINE_ID
AND L.ID=J.LABEL_ID
AND L.NAME LIKE "PGE_%"
ORDER BY L.NAME;
Posted by:
zookdj
13 years ago
You have both M.NAME and L.NAME in the select list. Try creating an alias for one of them, such as:
SELECT DISTINCT M.NAME AS MACHINE_NAME, M.IP, L.NAME AS LABEL_NAME
djz
SELECT DISTINCT M.NAME AS MACHINE_NAME, M.IP, L.NAME AS LABEL_NAME
djz
Posted by:
ATKlein
13 years ago
Hey guys,
That worked (sort of)! I added the alias as in zookdj's example, but the report comes back with duplicate data as:
1 AHS-027774-LT 10.7.6.145 AHS-027774-LT
2 AHS-RMH5-WS24 10.7.6.94 AHS-RMH5-WS24
3 AHS-035040-LT 10.7.6.32 AHS-035040-LT
4 AHS-014146 10.7.5.161 AHS-014146
5 AHS-C9-WS03 10.7.5.70 AHS-C9-WS03
6 AHS-025015-LT 10.7.6.157 AHS-025015-LT
Ideally, the report would have each unique "PG&E" label listed as a header and then the corresponding machine names and IP addresses as records underneath the unique headers.
Thank you so much!
-Alex
That worked (sort of)! I added the alias as in zookdj's example, but the report comes back with duplicate data as:
1 AHS-027774-LT 10.7.6.145 AHS-027774-LT
2 AHS-RMH5-WS24 10.7.6.94 AHS-RMH5-WS24
3 AHS-035040-LT 10.7.6.32 AHS-035040-LT
4 AHS-014146 10.7.5.161 AHS-014146
5 AHS-C9-WS03 10.7.5.70 AHS-C9-WS03
6 AHS-025015-LT 10.7.6.157 AHS-025015-LT
Ideally, the report would have each unique "PG&E" label listed as a header and then the corresponding machine names and IP addresses as records underneath the unique headers.
Thank you so much!
-Alex
Posted by:
GillySpy
13 years ago
Did you type it correctly? If so, did you regenerate the layout xml?
Posted by:
ATKlein
13 years ago
Hi Gerald,
Yessir. I typed it correctly and verified that I checked the auto-generate new layout box. This is the data I get with the same SQL query when I use the MySQL browser:
AHS-RMH5-WS24 10.7.6.94 PGE_AHS-5262155432
AHS-027750-LT 10.7.6.136 PGE_AHS-5262155432
AHS-027749-LT 10.7.5.85 PGE_AHS-5262155432
AHS-027771-LT 10.7.6.219 PGE_AHS-5262155432
AHS-027774-LT 10.7.6.145 PGE_AHS-5262155432
AHS-035040-LT 10.7.6.32 PGE_AHS-5262155432
Thank you!
-Alex
Yessir. I typed it correctly and verified that I checked the auto-generate new layout box. This is the data I get with the same SQL query when I use the MySQL browser:
AHS-RMH5-WS24 10.7.6.94 PGE_AHS-5262155432
AHS-027750-LT 10.7.6.136 PGE_AHS-5262155432
AHS-027749-LT 10.7.5.85 PGE_AHS-5262155432
AHS-027771-LT 10.7.6.219 PGE_AHS-5262155432
AHS-027774-LT 10.7.6.145 PGE_AHS-5262155432
AHS-035040-LT 10.7.6.32 PGE_AHS-5262155432
Thank you!
-Alex
Posted by:
GillySpy
13 years ago
Sounds like the column reference in the XML is the same for both of those columns, but could be something else. I suggest a support ticket.
Posted by:
ATKlein
13 years ago
Thanks so much for your help Gerald and DJZ. I'll get a ticket fired up on the hop! Have a great day :) -Alex
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.