Sunday, October 18, 2009

Jasper Reports Create a Union Query in iReport

This is a simple, step by step guide to creating a union query using Jasper's iReport.

For my sample, I am using a single table, "BookList", that contains data for a local library.

In my sample table, regular book titles have an ISBN number, but the library also has unpublished material that have an internal reference number called "LocalFileNumber".

For my Union Query, I want to select from this table the Author, Title, ISBN if available, and LocalFileNumber where ISBN is null.


Here is my sample data:

-- --------------------------------------------------------

--
-- Table structure for table `BookList`
--

CREATE TABLE IF NOT EXISTS `BookList` (
`id` int(11) NOT NULL auto_increment,
`Author` varchar(50) default NULL,
`Title` varchar(50) default NULL,
`ISBN` varchar(50) default NULL,
`LocalFileNumber` varchar(50) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

--
-- Data for table `BookList`
--

INSERT INTO `BookList` (`id`, `Author`, `Title`, `ISBN`, `LocalFileNumber`) VALUES
(1, 'Henry James', 'The Portrait of a Lady', '0141439637', NULL),
(2, 'T.S. Eliot', 'Collected Poems', '0151189781', NULL),
(3, 'William Gaddis', 'The Recognitions', '0844667404', NULL),
(4, 'John Smith', 'History of Our Library', NULL, 'JS12345678'),
(5, 'James Merrill', 'The Changing Light at Sandover', '0307263215', NULL),
(6, 'Wendy Jones', 'Library Policy Manual', NULL, 'WJ12345678');



So, we have 4 titles with ISBN numbers, and 2 titles that use the libraries internal reference number (highlighted in bold below):


mysql> select * from BookList;
+----+----------------+--------------------------------+------------+-----------
------+
| id | Author         | Title                          | ISBN       | LocalFileNumber |
+----+----------------+--------------------------------+------------+-----------
------+
|  1 | Henry James    | The Portrait of a Lady         | 0141439637 | NULL
      |
|  2 | T.S. Eliot     | Collected Poems                | 0151189781 | NULL
      |
|  3 | William Gaddis | The Recognitions               | 0844667404 | NULL
      |
4 | John Smith     | History of Our Library         | NULL       | JS12345678
      |
|  5 | James Merrill  | The Changing Light at Sandover | 0307263215 | NULL
      |
6 | Wendy Jones    | Library Policy Manual          | NULL       | WJ12345678
      |
+----+----------------+--------------------------------+------------+-----------
------+
6 rows in set (0.00 sec)


1. Start up iReport and create your connection.

2. At the Query section, add the 'BookList' table into the viewer and select Author, Title, and IBSN:





3.  Right click on WHERE and select Add a Condition:


 

4. The WHERE clause in our first select will be ISBN IS NOT NULL



5. Now that we have our first select with our WHERE clause, right click on ROOTQUERY and select UNION

 

6. Note that the UNION node now appears in the query explorer pane (just above the ORDER BY node).

 

7. Highlight the SELECT node under Union.

 

8. Double click on the BookList table to add it into the pane. This time we will select Author, Title, and LocalFileNumber.





9. Right click on the WHERE node for the query and select Add Condition:





9. For our condition on this select, we will now enter WHERE ISBN IS NULL




10. Expand all of the relevant nodes so we can see our query:




11. Now, at the bottom of the Query Builder, select the SQL tab:




12. From here, we can edit our query so that both ISBN from the first select and LocalFileNumber from the second select are selected as "BookCode" as shown below:





SELECT
     booklist.`Author` AS booklist_Author,
     booklist.`Title` AS booklist_Title,
     booklist.`ISBN` AS BookCode
FROM
     `booklist` booklist
WHERE
     ISBN IS NOT NULL
UNION
SELECT
     booklist.`Author` AS booklist_Author,
     booklist.`Title` AS booklist_Title,
     booklist.`LocalFileNumber` AS  BookCode
FROM
     `booklist` booklist
WHERE
     ISBN IS NULL


So we now have a query that will return Author, Title, and BookCode (ISBN or LocalFileNumber when ISBN is null).

One item you may note above is that we did not need to qualify our alias BookCode with quotation marks.

13. Click OK and review your query: 








No comments: