How to union tables in Crystal reports

[Helenlinz]

Hi I am new to CR and I'd like a help on how to create a union query to combine inforamtion from more than one tables (the tabbles have same structure however hold data in different time periods). Thanks,

Helen

 

[lbass]

You should always specify your version of CR, as the solutions differ based on version. In 9.0 and above, you can go to database->database expert->add command and enter a query there. The exact syntax depends upon your datasource and connectivity.

To do a union, you need to add fields in the second part of the union in the same sequence as in the first, with corresponding fields being of the same datatype. A union will "merge" corresponding fields into one field, taking on the label of the field as it appears in the first part of the query. Since you must have the same number of fields in both parts of the query, you need to add fake fields if necessary to create the symmetry. A "union" will result in distinct rows, while a "union all" will return all rows generated by both halves of the query. Since a union is evaluating for distinct records, it is slower.

The query would look something like this:

SELECT
table1.`date`,table1.`amount`, table1.`name`
FROM
`table1` table1
UNION
SELECT
table2.`date`,table2.`amount`, table2.`name`
FROM
`table2` table2

If you are unsure of the syntax, go to another report using the same database and go to database->Show SQL Query and replicate the syntax shown there.

If you need to add an "Order by" clause, it should be at the end of the final clause and should reference the field to sort by based on its sequence in the query. If you wanted to order by the amount field in the example above, you would use the following at the end of the entire query:

Order by 2

-LB