Union

[masha2008]

Ive created two different Queries
1st  Where ACCOUNTS = CC
2nd Where ACCOUNTS = BB

Im trying to create a union now where 1st column would be Accounts by CC, and 5th column Accounts by BB

When I rename my 2nd query Accounts column so it can be different, it wont let me pull it to the report....It recognizes it same as ACCOUNTS from the first query..
How can I avoid that and make them separate when I make a Union

As well as when I try to run it I get this error mes "The queries for the set operation are incompatible" When both queries have exactly the same Data items, just Accounts are joined by two different keys.

Thank you

Thank you

[blom0344]

The trick with Union constructions is to add a constant to each subset to be able to distinguish the origin of rows after the resultset is returned:

Code Select Expand



SELECT 'FIRST_BATCH',T1.C1,T1.C2 FROM ...............................
UNION
SELECT 'SECOND_BATCH',T2.C1,T2.C2 FROM ...............................
UNION
SELECT 'THIRD_BATCH',T3.C1,T3.C2 FROM ...............................

You can simply add the constant as data-items to the query in Cognos.

[masha2008]

Thank you very much for helping!
Im still having trouble with this report
IN query1 I pulled all the columns that I need including Acct Key
In query2 I pulled one column CC_Key
Query3 I joind Acct_Key and CC_key, and pulled just Account Number and other Columns that I need.
I did the same with Query 4,5, and 6 but instead I joined Acct Key by BB key...
Then I created a Query 7 where I  UNION together Query 3 and Query 6......
When I do that it gives my only one Account Data Item, I tried creating a data Item in Query4 by BB, it still wont work....
Where do I create my Data Items? and how do I Union them ?
Please let me know if Im doing anything wrong?
Thank you for your help

[Suraj]

You can create data items in the unioned query after union.