Text only | Text with Images

COGNOiSe.com - The IBM Cognos Community

IBM Cognos 8 Platform => COGNOS 8 => Report Studio => Topic started by: phoenixfire on 27 May 2009 07:12:48 PM

Title: Merging two queries
Post by: phoenixfire on 27 May 2009 07:12:48 PM
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..
Title: Re: Merging two queries
Post by: vinay17in on 27 May 2009 08:57:24 PM
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


Title: Re: Merging two queries
Post by: Gollapudi on 28 May 2009 06:24:02 AM
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.
Title: Re: Merging two queries
Post by: phoenixfire on 28 May 2009 12:22:18 PM
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.
Title: Re: Merging two queries
Post by: Rajaggopal on 29 May 2009 02:14:51 AM
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...
Title: Re: Merging two queries
Post by: Gollapudi on 31 May 2009 11:51:45 PM
QuoteYou 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.
Title: Re: Merging two queries
Post by: phoenixfire on 02 Jun 2009 06:20:31 PM
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.
Title: Re: Merging two queries
Post by: Gollapudi on 19 Jun 2009 02:22:22 PM
Most Welcome  ;D
Title: Re: Merging two queries
Post by: kristielee on 21 Jul 2009 12:48:35 PM
Is there a simple way in report studio to find out the data types of each item, without leaving report studio?
Text only | Text with Images