/bundles/itninjaweb/img/Breadcrumb_cap_w.png
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   [ - ] Hide 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.
Answer this question or Comment on this question for clarity

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