I am not sure how to phrase this but I will try my best. I am trying to union two tables within iReports and then use the fields in the report without having to use a sub-report. How can I do that? The following is an example of what I am doing but my actual has 18 columns in each table I need to access ut the example nly has 4 so I can keep it short here. How can I make this happen?
My SQL:
SELECT fund_id, subplan_id FROM subplan UNION (I have also tried INTERSECT, INNER JOIN, OUTER JOIN) SELECT employer_id, employer_name FROM employer
I would like for them to be independent fields; but iReports only recognizes fund_id and subplan_id and ignores the employer_id and employer_name from the second select statement. No matter how I do the SQL it doesnt work. Either I get syntax error or it just displays the list under fund_id and subplan_id. SQL and MySQl recognize it but iReports wont accept any code other than the union.
If you all could help me out with getting this to work without having to use sub-reports it would be really great.
How can I get iReports to recognize the second set of fields in a second select statement?
in Products
Posted
Hi Guys,
I am not sure how to phrase this but I will try my best. I am trying to union two tables within iReports and then use the fields in the report without having to use a sub-report. How can I do that? The following is an example of what I am doing but my actual has 18 columns in each table I need to access ut the example nly has 4 so I can keep it short here. How can I make this happen?
My SQL:
SELECT fund_id, subplan_id
FROM subplan
UNION (I have also tried INTERSECT, INNER JOIN, OUTER JOIN)
SELECT employer_id, employer_name
FROM employer
Results in both MySQL and SQl are below:
fund_id subplan_id
1F789464 46578913
79846 4679
98789789 98789789
F789654E 78946456
100000 Klingon Empire
158975 Romulan Empire
215524 Kardisan Empire
235687 Brein Federation
325658 White House
489654 Stargate COmmand
I would like for them to be independent fields; but iReports only recognizes fund_id and subplan_id and ignores the employer_id and employer_name from the second select statement. No matter how I do the SQL it doesnt work. Either I get syntax error or it just displays the list under fund_id and subplan_id. SQL and MySQl recognize it but iReports wont accept any code other than the union.
If you all could help me out with getting this to work without having to use sub-reports it would be really great.
Warren