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
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
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;
Answered 03/09/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
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
Answered 03/09/2011 by: zookdj
Second Degree Blue Belt

Please log in to comment
0
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
Answered 03/09/2011 by: ATKlein
Senior Yellow Belt

Please log in to comment
0
Did you type it correctly? If so, did you regenerate the layout xml?
Answered 03/09/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
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
Answered 03/09/2011 by: ATKlein
Senior Yellow Belt

Please log in to comment
0
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.
Answered 03/09/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
Thanks so much for your help Gerald and DJZ. I'll get a ticket fired up on the hop! Have a great day :) -Alex
Answered 03/09/2011 by: ATKlein
Senior Yellow Belt

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