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!
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
You could use SQL Minus or Except
https://www.techonthenet.com/sql/minus.php
(https://www.techonthenet.com/sql/minus.php)
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!
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])