COGNOiSe.com - The IBM Cognos Community

Hey guys

I have an issue on query items. I have 2 query items those two query items are comming from two different data bases.
Let's say I hav two query items

GAC ID is of type INteger and VACId is of type String. But those two Query items contains  same data. I want to make those two query items into one query item and I want to create prompt  after merging it into one query item . Is it possible to cast these two query items in to one query item in Frme work Manager if so give me a clue how to do this or in Report studio if so give me a clue to solve this issue.

As per our clients requirements we wants to do it in frame work manager.

Any one can help me to solve this issue.

Help can be appreiatable.


Thanks 

Hi,

I verified in Frame work model, it is possible. But i think may it cause the Performance of the query execution.

To solve ur problem.

1. The data source which ur using should set the query processing property to 'limited local'
2. Take a new Qeury Subejct
3. Add the string column
4. cast the string column to integer for e.g. cast(stringcolumn,integer)
5. Create a relation joining both ur number query subject and this newly created query subject by the cardinality 0..n and 0..n
6. Create a new query subject
6. Add new query item.
8. add the following code.
Ã,  Ã,  case
Ã,  Ã,  when [scott].[T1].[VALUE1] is null then [scott].[New Query Subject].[VALUE2]
Ã,  Ã,  else [scott].[T1].[VALUE1]Ã, 
Ã,  Ã,  end
9. Test the query subject it will give u the number column values and string column values merged into one column.

I have taken two table T1(number) AND T2(string).

But here we used Local Processing concept and Full Outer Joins, I think this will cause the performance.

i hope this will help u..

Pls let me know is this solve ur problem or not.

regards.

Hi Ravi,

You are  using Which database? I think U r using Oracle. We r using Db2 main frame database. 
In the case of Mainframe database what will be the code. I think scott won't work. If u have any clue pls try to share it to me.
case
    when [scott].[T1].[VALUE1] is null then [scott].[New Query Subject].[VALUE2]
    else [scott].[T1].[VALUE1] 
    end

Thanks.