Merging two queries

[phoenixfire]

Hello,
Can anyone help me out here?
I have three different queries that are based on different criteria. I have to merge the User Id's from these three queries into a fourth query.

I tried union/join where I merged two queries into a query and then merged this query and the third query. Cognos doesn't allow me to do the second part.

Am I missing something here?

Thanks a lot in advance..

[vinay17in]

While performing set operations, you have to take care of few things

1. Same number of query items in both the queries
2. The data type of of corresponding query items should match
3. If you have any dummy columns with constant values in any of the queries, CAST them same data type as corresponding query item


Example
Query 1
----------------------------
QueryItem1 (String)
QueryItem2 (Date)
QueryItem3 (Integer)

Query 2
-------------------------------
QueryItem 1
QueryItem2 (CONSTANT) - CAST(2009-12-31, DATE)
QueryItem3 (CONSTANT) - CAST(0, INTEGER)

Hope this helps

[Gollapudi]

And the other thing is you dont have to create 2 Unions here, with a single Union Operator you can union more than 2 Queries in Report Studio, but make sure that your following the above criteria mentioned by vinay..

Thanks,
Gollapudi.

[phoenixfire]

Thanks for your answers. But I already have the same number of query items in all the three queries. The criteria for filtering is different. That's all!!

You mentioned something about merging more than two queries using union. How is it possible? Report studio gives me a message saying 'only two queries can be joined'.

It doesn't really matter if I use union or join.

Thanks.

[Rajaggopal]

For union its not enough if you have just the same number of query items.... the name, type and order of query items should also match in each of the queries being unioned. Make sure you have the queries that way and perform a union.

Also if you are using Cognos 8.4 it is advisable that the aggregate functions of the data items match as well... since we experienced some problems related to that...

[Gollapudi]

You mentioned something about merging more than two queries using union. How is it possible? Report studio gives me a message saying 'only two queries can be joined'.

Hi Please check the attached sample report which I have done in 8.2 itself....

Thanks,
Gollapudi.

[phoenixfire]

Thank you so much!
I was trying to figure out if three queries can be merged in union. This helps me a lot!

Thanks again.

[Gollapudi]

Most Welcome  ;D

[kristielee]

Is there a simple way in report studio to find out the data types of each item, without leaving report studio?