Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

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

Former Member
Former Member replied

J,

That's good news.

The 1st order of business is to get both Date field into the same data type. Does Infomix support the CAST or CONVERT functions?

If not, does Infomix have any equivalent functions?

For reference: CAST and CONVERT are used to change one data type to another.

2nd Do the UNION ALL with just the two tables in a subquery. Link any other tables to the subquery, that way you are not pulling from them twice.

3rd If Infomix is anything like SQL Server, you will want to make sure that you are using "fully qualified table names" in your SQL.

Jason

0 View this answer in context

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question