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
Jason Long 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