How to do Union All

[Former Member]

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

[amrsalem1983 · Accepted solution]

i dont know much about SQL Server Databases in Oracle you can do it as below

select id,name from table1

union all

select id,name from table2

and you can do the any restrictions you want in the "Where" section in both of the select statments.

SQL server Databases hasnt the same keywords??

i think it may has it, please find out.

good luck

Amr

[Former Member · Accepted solution]

I am not using SQL server I am using Infomix via ODBC connection.

The funniest thing is, when I asked my colleague what is the command we use to open and close databases (to see if he has any idea as he is working here for past 20 years), he said via ODBC you put the name of database and it connects it.

I was like ok thank you.

Regards

Jehanzeb

[amrsalem1983 · Accepted solution]

sorry Jehanzeb, i thought you were using SQL Server database,

anyway "UNION ALL" is supported upon SQL server databases.

i wish you can find some solution for ur issue with the informix

good luck

Amr

[Former Member · Accepted solution]

Thanks Amr, I know you are a great help around here. I am sure I'll be able to find out something before the dbadmin comes.

Here is the link which I think would be useful

[http://www.dbcenter.cise.ufl.edu/triggerman/InfoShelf/index.html]

I'll post something once I find out.

Many thanks

Regards

Jehanzeb

[amrsalem1983 · Accepted solution]

i think its supported in informix too

check the SELECT statment syntax in informix

http://www.dbcenter.cise.ufl.edu/triggerman/InfoShelf/sqls/01select.fm.html#148676

good luck

Amr

[Former Member · Accepted solution]

Thanks Amr, Infact I was on the same page.

I read it but it doesn't say anywhere how to get the two databases work in one command.

I am already in SAE1 and used a "Add Command" and wrote my SQL

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

Close Database

Database Klab

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 <> ""

Close Database

Database SAE1

however this gives syntax error.

If I use Union, it says the corresponding columns should match with the other columns.

still searching.....

Regards

Jehanzeb

[amrsalem1983 · Accepted solution]

to do a union all columns should be matching the same datatype

id ,name ,age

id1,name1 ,age1

number,char,number

check the datatypes for both.

good luck

Amr

[Former Member · Accepted solution]

Well they aren't

because, both of them are two different tables and types. The only thing which is matching is Reason Code and description

Here how it is

Klab:

Credit_no = String

Reason_code = Number

Reason_Desc = String

Credit_Date = Date

Credit_Status = Number

line_no = number

line_type = String

Stock_code =String

Credit_qty = number

Where as in SAE1 it is

SAE1:

Order_no = Number

Order_status =Number

Date_entered = date

FOC = String

Reason_code =Number

Reason_desc =String

Order_qty =number

Regards

Jehanzeb

[Former Member · Accepted solution]

J,

The problem isn't the UNION or UNION ALL commands. It's the fact that you are pulling from 2 different databases.

Questions that need to be answered...

1) Are both databases Infomix? If not what is the other RDBMS?

2) Are both databases on the same server? Is so, are they both running in the same instance of Infomix (assuming they are both Infomix)?

Worst case scenario is that you will have to bull both databases into MS Access, perform a UNION query there and then link CR to the Access query.

As far as the dates part you will have to use a function(s) to make both dates the same data type. If you end up resorting to Access you shouldn't have to big of a problem doing that there.

Jason

PS. You have a secretary??? Are you guys hiring???

[Former Member · Accepted solution]

Jason don't mention the worst scenario, I will kill my director before I even think of using Access.

Yes both are Informix database and both are on the same server.

The results do come fine, up till when there is an exact match in both tables however the darn thing takes forever to produce results.

Additionally it should get results (reason code and their records) from both tables and not just exact match but if the table holds the record it should print it.

I have tried using left outer but did not make any difference to the results, maybe that is because of the two different dbs?

Regards

Jehanzeb

[Former Member · Accepted solution]

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

[amrsalem1983 · Accepted solution]

Jehanzeb,

they must to to be same datatype to use union or union all.

this is the only option you have.

good luck

Amr

[Former Member · Accepted solution]

Morning Jason,

Sorry I had to run back home as it was my horse riding time...Can't miss that at any cost. not because I love horses but because I love the trainer ).

Anyhow back to work....

Yes both date types are same data type of Date and both have been used in the two Command Queries.

Did you mean to create a third Command query and add both of those dates into that query and make a date command as one? or did you mean to join the both dates together like an inner join?

The question is how to open and close the two databases into the command query. I have tried

Open Database

.....

Close Database

That doesn't work, I have to find out what is the command which I can use in the Command query to combine the two databases. Then I can built one Command query for the Date type and search from it.

I am trying one way now, which is,

Created 5 tables from the Command query, 2 from each table and 5th one is from SAE database which holds only reason code.

So now they look like this

[LINK to Table relationship|http://i46.photobucket.com/albums/f115/jaytheguru/tablelinkingnow.jpg]

Though the query is way too slow to run.

I am trying though....

lets see what happens.

[Former Member · Accepted solution]

J,

You'll want to get everything into one SQL Command...

Looking at your diagram, I have to wander how long that thing takes to run???

What I'm talking about is the order in which the SQL gets executed.

For example:

SELECT
a.Field1,
t3.Field2
FROM (
SELECT
t1.Field1,
t1.Field2,
t1.Field2
FROM Table_one AS t1
WHERE t1.Fieldx= whatever
UNION
SELECT
t2.Field1,
t2.Field2,
t2.Field2
FROM Table_two AS t
WHERE t2.Fieldx= whatever) AS a
INNER JOIN Table_three AS t3 ON a.Field2 = t3.Field4

Due to performance issues, try not to mix anything with Commands, not even other commands. You should be able to get EVERYTHING into one SQL Command.

Jason

[Former Member · Accepted solution]

Jason,

you are right that the report does take ages to run however we have to keep in mind that there are two different databases here which needs to be connect and disconnect to get the correct data and that I cannot find how to do in the sql(Informix).

Creating something in one command could not be hard providing the tables are in one database.

There is another thing which we need to keep in mind and that is the conditions which are applied on each record.

For example:

Order number needs to distinctly counted when FOC status="Y" where as from 2nd table Order quantity does not require this status and if the status is on at the time it will produce the incorrect results.

Similarly on the 2nd table Database

Credit number needs to be distinctly counted when the status =2 whereas from 2nd table Credit2 quantity needs to be counted when line type="S" and line_no<3.

Both table statuses would not work if I enable them in one table.

The common thing in between two tables from two databases is Reason code which needs to be left outer join.

Trying another method now....

[Former Member · Accepted solution]

Rightio, this method tend to work really fast and accurate however the problem with this method is that it holds the date range in the query and shows all the reason codes regardless of the match from both tables (i am using left outer join - that must be it why its showing all range however what I really want is to show only the range which holds the value however being a left outer join it will show the values from both tables or else it only looks for the exact match).

I wrote a query like this

sae1_live
SELECT lab_reasons.reason_desc, lab_reasons.reason_code
 FROM   maxmast.lab_reasons lab_reasons
 WHERE  ((lab_reasons.reason_code>=1 AND lab_reasons.reason_code<=99) OR
(lab_reasons.reason_code>=100 AND lab_reasons.reason_code<=199) OR (lab_reasons.reason_code>=200
AND lab_reasons.reason_code<=299) OR (lab_reasons.reason_code>=300 AND
lab_reasons.reason_code<=399) OR (lab_reasons.reason_code>=400 AND lab_reasons.reason_code<=499)
OR (lab_reasons.reason_code>=500 AND lab_reasons.reason_code<=599) OR
(lab_reasons.reason_code>=600 AND lab_reasons.reason_code<=699) OR (lab_reasons.reason_code>=700
AND lab_reasons.reason_code<=799) OR (lab_reasons.reason_code>=800 AND
lab_reasons.reason_code<=899) OR (lab_reasons.reason_code>=900 AND lab_reasons.reason_code<=999))
 EXTERNAL JOIN lab_reasons.reason_code={?sae1_live: FOC_Records.reason_code} AND
lab_reasons.reason_code={?klab_Live: Credit_Records.reason_code}

sae1_live
SELECT lab_reasons.reason_code, lab_reasons.reason_desc, order_header.foc, order_header.order_no,
order_lines.order_qty, order_header.date_entered, order_header.order_status
 FROM   maxmast.lab_reasons lab_reasons, maxmast.order_header order_header, maxmast.order_lines
order_lines
 WHERE  (lab_reasons.reason_code=order_header.reason_code) AND
(order_header.order_no=order_lines.order_no)AND (order_header.date_entered>={ts '2008-09-18 00:00:00'}
AND order_header.date_entered<={ts '2008-09-18 00:00:00'}) AND order_header.foc='Y' AND
order_header.order_status<>0
 EXTERNAL JOIN FOC_Records.reason_code={?sae1_live: lab_reasons.reason_code}

klab_Live
 SELECT lab_credits.credit_date, lab_reasons.reason_code, lab_reasons.reason_desc,
lab_credits.reason_code, lab_credits2.credit_qty, lab_credits2.line_no, lab_credits2.line_type,
lab_credits.credit_status, lab_credits.credit_no, lab_credits2.stock_code
 FROM   roger.lab_credits2 lab_credits2, roger.lab_credits lab_credits, maxmast.lab_reasons lab_reasons
 WHERE  ((lab_credits2.credit_no=lab_credits.credit_no) AND (lab_credits2.order_no=lab_credits.order_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<>''AND (lab_credits.credit_date>={ts '2008-09-18 00:00:00'} AND
lab_credits.credit_date<={ts '2008-09-18 00:00:00'})
 EXTERNAL JOIN Credit_Records.reason_code={?sae1_live: lab_reasons.reason_code}

and it works superbly but if you look at the date range, it is placed within the code.

If I take the date range out and puts it in the Record Selection area of the report, the report laggssss...I think there must be something which could be causing that?

[Former Member]

This has been answered. The solution: I asked my db admin to create a Union all table for me in the database or else.

He did and problem solved

[Former Member]

J,

You always come up with the biggest pain in the butt problems. In SQL Server you can setup a linked server that will allow you you use databases from multiple servers or even database types (as long as the other DB has an OLE BD connector).

If I remember correctly you are on an Infomix DB. You can search google for similar functionality in infomix, or if the other DB is SQL Server you may be in luck.

Basically, one query can only be sent to one server at a time. If both databases are on the same server, you may be able to call both using a 3 part table name. Example: FROM DatabaseName.SchemaName.TableName

Jason

[Former Member]

Jason my friend! I guess I post things which are no where to be found.

I am certainly using Infomix db (which was built in 18th century...Definitely by the looks of it, as it doesn't support anything. Infact the other day IBM themselves said to change this old piece of .... into a new one but no one seems to be caring so far).

I did write an SQL command like this

select oh.order_no, ol.order_qty,oh.reason_code, lr.reason_desc,
oh.order_date, 'SAE' as Location
from order_header oh
inner join order_lines ol
    on oh.order_no = ol.order_no
inner join lab_reasons lr
    on oh.reason_code = lr.reason_code
where oh.foc='Y'and
oh.Order_status <>0
union all
select lc.credit_no, lc2.Credit_qty, lc.reason_code, lr.Reason_desc,
lc.CreditDate, 'KLAB' as Location
from klab.lab_credits lc
inner join klab.llab_credits2 lc2
    on c.credit_no = lc2.credit_no
inner join klab.lab_reasons lr
    on lc.reason_code = lr.reason_code
where lc.credit_status= '2;
and lc2.line_no <3
and lc2.line_type = 'S'
and lc.credit_no <> ''
and lc2.Stock_code <> ''

But the stupid Informix cannot handle it.

I don't know if both databases are on the same server I presume they are, in any case I cannot use the Union all as I don't even know the correct syntax under Infomix.

When I looked at one of the programmer's codes it says

Database SAE1

...

Close Database

Database Klab

...

Close Database

For obvious reasons this ain't going to work.

I have left a message with my good for nothing secretary to see if he can arrange a meeting with dbAdmin, he said he'll see if someone comes around and he'll ask one of them to come to my office.

So lets see what happens....

To tell you the truth I have to do everything here, not just report development, application development and biggest pain search the Database and in some cases fix it. Crazy!!....

Will update here once I get anything

Regards

Jehanzeb