COGNOiSe.com - The IBM Cognos Community

IBM Cognos Analytics Platform => Cognos Analytics => Reporting => Topic started by: peewan on 21 Nov 2019 12:18:07 PM

Title: Compare 2 queries from 2 tables
Post by: peewan on 21 Nov 2019 12:18:07 PM

Table A: Query A

S-Code        Epic code
010             B
020             B
030             B
032             B

Table B: Query B
Code        State
010             WY
020             WA
021             WA
022             MN
030             OR
033             OR

I want to compare the value to find the difference value between 2 queries from 2 difference table, to see which code is missing, so I can do an update for documentation later on.

But the join S-CODE TO CODE, wasn't work for me. the join query return all matching code like: 010, 020..only not 021 or 022
Any helps? Thanks a lot!
Title: Re: Compare 2 queries from 2 tables
Post by: CognosPaul on 21 Nov 2019 02:00:54 PM
You need to do what Cognos calls a stitch query.

Full outer join between the tables, with a coalesce or nvl on the key.

with querya (scode, epicCode) as  (select scode, epicCode from tablea)
, queryb (scode, state) as (select code, state from tableb)

select coalesce(querya.scode, queryb.scode) as scode
, querya.epiccode
, queryb.state
from querya
full outer join queryb on querya.scode=queryb.scode
Title: Re: Compare 2 queries from 2 tables
Post by: Andrei I on 22 Nov 2019 07:18:19 AM
You could use SQL Minus or Except
https://www.techonthenet.com/sql/minus.php
(https://www.techonthenet.com/sql/minus.php)
Title: Re: Compare 2 queries from 2 tables
Post by: peewan on 22 Nov 2019 10:15:16 AM
Quote from: CognosPaul on 21 Nov 2019 02:00:54 PM
You need to do what Cognos calls a stitch query.

Full outer join between the tables, with a coalesce or nvl on the key.

with querya (scode, epicCode) as  (select scode, epicCode from tablea)
, queryb (scode, state) as (select code, state from tableb)

select coalesce(querya.scode, queryb.scode) as scode
, querya.epiccode
, queryb.state
from querya
full outer join queryb on querya.scode=queryb.scode


Thanks for the response. I don't know how to write/use SQL statement in Cognos :(, i need to learn bout this;  but i took your advise and search about stitch query and full outer join.
Left join: 1:1, 0:n
Right join:0:n, 1:1
Full outer join: 0:n, 0:n

Thank you so much!
Title: Re: Compare 2 queries from 2 tables
Post by: CognosPaul on 22 Nov 2019 10:20:31 AM
You don't need to write any SQL, that was just an example. Use the join item from the insertable objects and set it exactly as you said.

If the data items window make sure to use the coalesce function: coalesce([Query1].[sCode],[Query2].[epicCode])