How to use two different values from same table

[mike8869]

I'm trying to creat a report that will show the character type code and the character value. There are two Char_Type_Cd - MTRSIZE and IRR ACRE. The two codes have different values associated with them. I'm able to show one or the other but not both at the same time. I've tried using two queries, seperate filters and I can't get any results when trying to get both values.
Any help would be appreciated.
Thanks

 

[blom0344]

Union a set of 2 queries. If the seperate queries yield the correct output, then a union will show both results.
A union is possible with:

(1) same number of query items for both queries
(2) same datatypes for the corresponding items between queries

Ties Blom

 

[KristieLee1]

@blom0344....its been a while since I was on this site, and you are still giving folks the Union idea. I HATE unions...but I love to hate them. eh

 

[blom0344]

You make it sound like I am trying to get them to use a perverted solution. Unions are a powerful solution in some cases.
It is beyond me why anyone would actually hate them. Do you have problems with the concept?

Why not use your post to offer a better or alternative solution?

Ties Blom

 

[KristieLee1]

Unions are very powerful, I just struggle with them. I struggle with the concept, and am annoyed that I have to have everything labeled and sorted exactly the same way in each query in order for it to work. Thus causing me to have to create bogus data items just to get things to match up correctly.

BTW..it was a joke. I love all your feedback, and your comments and posts are one of the main reasons I come to this site. Perhaps my sarcasm was lost in the message. I meant no disrespect, and would never dream of assuming that you would suggest a perverted solution. My apologies.

 

[blom0344]

Okay, this may all be due to the fact that I need to recompile everything into Dutch , which may in some cases cause a bit of a shortcut in the circuits..

So, no offence taken, we're back on track and thank you for the nice words too..

I often seem to suggest the union approach cause I expect lots of people not to be familiar with the set based concept.

I think best practice is to make sure that you copy a query , thereby making sure that the order of dataitems is the same for every set. you can then redefine step by step even adding constants where those are needed

Ties Blom

 

[KristieLee1]

Would it be possible for you to provide an in depth example or direct me to one that shows the actual union?
Am I correct in stating that each query must have the same number of data items, in the same order, and must all be the same data type, and have the same data item name?

My struggle is that I have two different types of information, which is why I have two different queries. If they were all the same information, I could just do it in one query.

If you could direct me to a decent site that shows exactly how a union should work within Cognos (but not the Cognos documentation because that gets me nowhere) I would really appreciate it.

Thank you! And Dutch?

 

[blom0344]

the same number of data items, in the same order, and must all be the same data type, and have the same data item name

Yes, except for the dataitem name (no reason to be the same)

Both SQL and Cognos will take the first set of the union to determine naming.

Procedure:

1. Create query1
2. Create query2 (copy query1 for instance

Make sure that order number of dataitems and type are same

3. Drag a union from the toolbox
4. In the query editor drag query1 and query2 into the union (at the right side)
5. The outcome will be called query3

Unfortunaly Cognos has refrained from adding (all) dataitems into query3
You have to manually drag them into the query (this is the part that is not obvious).
Query3 is empty right after creation

The reason is that you do not need to select ALL dataitems. If your query1 and 2 have each 10 dataitems, then it is okay to use just 6 in query3 (= possible, not logical)

You use query 3 for reporting purposes

Ties Blom

 

[KristieLee1]

AWESOME! I'm going to try this as soon as I get a chance. Thank you for your help.