Panopea Consulting - iMIS Blog

UNIONs 101

A favorite reporting technique of mine is the use of UNION queries, especially within a VIEW. Let's start first with UNIONs and I'll cover VIEWs in a later posting.   

Here's an example of iMIS reporting that can be nicely handled using a UNION query. Let's say you want to send a letter to prospective members on a regular basis and you want to include these two groups of records in iMIS:

1. Non-member records that have been added in the last 30 days. These records would be found in the Name table.
2. Event registrants that have not been added to the Name table. These records would be found in the Orders table.

You don't want to have to do two sets of letters, so you want both of these sets to be in the same results set. How can that be done? That's where the UNION query can come to your rescue.

SQL Books Online defines the UNION operator:

Combines the results of two or more queries into a single result set consisting of all the rows belonging to all queries in the union. This is different from using joins that combine columns from two tables.

Two basic rules for combining the result sets of two queries with UNION are:

• The number and the order of the columns must be identical in all queries.
• The data types must be compatible.

In other words, the UNION operator lets you "add" together two or more SELECT statements.

So continuing with the example situation, here are the SQL statements to get each of the sets of data described.

Non-Member Records might be found this way:

select FULL_NAME, FIRST_NAME, LAST_NAME, PREFIX, FULL_ADDRESS
from Name
where MEMBER_RECORD = 1 and COMPANY_RECORD = 0
and STATUS = 'A'
and datediff(day, DATE_ADDED, getdate()) <= 30

The Event Registrants not in the Name table might be found this way:

select FULL_NAME, FIRST_NAME, LAST_NAME, PREFIX, FULL_ADDRESS
from Orders
where SOURCE_SYSTEM = 'MEETING' and ST_ID = ''
and and datediff(day, ORDER_DATE, getdate()) <= 30

To get these two sets of records together into one result set, you use the UNION operator like this:

select FULL_NAME, FIRST_NAME, LAST_NAME, PREFIX, FULL_ADDRESS
  from Name
  where MEMBER_RECORD = 1 and COMPANY_RECORD = 0
  and STATUS = 'A'
UNION
select FULL_NAME, FIRST_NAME, LAST_NAME, PREFIX, FULL_ADDRESS
  from Orders
  where SOURCE_SYSTEM = 'MEETING' and ST_ID = ''

That's it! That's all you need to do to get both sets of records to be returned in one result set.

One last thing I'll mention about the UNION operator is that use of the UNION operator by definition eliminates any duplicate records. If you actually want the duplicates included, you simply add the ALL keyword, as in <select stmt 1> UNION ALL <select stmt 2>. 

Checkboxes on Crystal Reports

I can never remember the right character codes to use for making checkboxes show on a Crystal Report. Lucky for me, there's a great KB article on the Business Objects website that I look up to see the right values. These values work when formatted using the WingDing font:

CHR(254) - displays a box with a check mark
CHR(253) - displays a box with an X
CHR(111) - displays an empty box
CHR(252) - displays a check mark only
CHR(251) - displays an X only

Crystal Reports based on Stored Proc

If you have an 8.5 version Crystal Report that does an insert or update of data and you're running that report in a version of iMIS that uses the Crystal 9 runtime (version 10.2 or higher), you may be getting funny results. You will likely find when you run your report within iMIS that your insert or update happens but there are no records returned as a result set. Running the report outside of iMIS returns a result set as expected.

The reason this is happening is that there's an issue with the Crystal version 9 runtime that causes iMIS runs the stored proc twice each time you think you are calling it once by running the report. So the work gets done but your report is getting the result set from the second time it runs, hence no records returned. This is a problem using any stored proc that changes data. If all it is doing is selecting the data, it doesn't matter so much that it runs twice.

The solution is to upgrade your report to Crystal 9.