How to do Union All

Dear all,

I have created a report which runs off from 2 subreports, and as you are aware that running reports like this is not a good idea as it hits the performance pretty badly.

So I decided to use SQL in my report.

I created two tables

1: Command - Klab

2: Command_1 - SAE1

Where Command is the name of the table and SAE1,Klab are databases.

I wrote a SQL command like this

Command

select Order_header.order_no, order_lines.order_qty,order_header.reason_code, lab_reasons.reason_desc,
order_header.date_entered
from order_header, order_lines, lab_reasons
Where
 order_header.order_no = order_lines.order_no and
 order_header.reason_code = lab_reasons.reason_code
and order_header.foc="Y"and
order_header.Order_status <>0

Command_1

Select 
lab_credits.credit_no, lab_credits2.Credit_qty, lab_credits.reason_code, lab_reasons.Reason_desc,
lab_credits.Credit_Date
From 
lab_credits, lab_credits2,lab_reasons
Where
lab_credits.credit_no = lab_credits2.credit_no 
and lab_credits.reason_code = lab_reasons.reason_code 
and lab_credits.credit_status= "2"
and lab_credits2.line_no < 3
and lab_credits2.line_type = "S"
and lab_credits.credit_no <> ""
and lab_credits2.Stock_code <> ""

This is how I created two commands.

Now on the link side I linked the reason code with the other table reason code.

This however runs slow too and what I am looking is to Union the two so that I can have only one table and I should be able to run the report from that one table.

Is it possible?

Please note: The two tables are from two different databases with two different date values which need to be keep in mind. I used a formula in the report selection record like

{Command.date_entered} in {?StartDate} to {?Enddate} OR
{Command_1.credit_date} in  {?StartDate} to {?Enddate}

To show the report accordingly however the report does not produce the correct results nor produces the results faster.

Any suggestions would be highly appereciated.

Many thanks

Kind Regards

Jehanzeb